I am trying retrieve row and column number for following strings. The numbers that follow the R are the row no and numbers that follow the C are column numbers. Also as you can see some of them are 4 digits and some of them are 3 digits. How do I use a single function to retrieve the row and column number respectively.
NSFR80R0020C0030 e.g Row is 0020 Column is 0030 C75.01R2220C050 e.g Row is 2220 Column is 050 C76R380C010 e.g Row is 380 Column is 010
I started by using the patindex but not sure how to determine based on it. Could somebody help
SELECT PATINDEX('%R%C%','C75.01R2220C050')
Advertisement
Answer
This forum isn’t for solving your programming tasks. But for the fun of it, I’ve done it for the row number:
create FUNCTION getRow ( @s varchar(max) ) RETURNS varchar(4) AS BEGIN DECLARE @Result varchar(4) declare @ind int SET @ind = PATINDEX('%R[0-9][0-9][0-9][0-9]%', @s) if @ind > 0 set @Result = SUBSTRING(@s, @ind + 1, 4) else begin SET @ind = PATINDEX('%R[0-9][0-9][0-9]%', @s) if @ind > 0 set @Result = SUBSTRING(@s, @ind + 1, 3) else set @Result = '' end RETURN @Result END