sql server - Retrieving row and column number from within a string in sql

Question

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')

question from:https://stackoverflow.com/questions/65645224/retrieving-row-and-column-number-from-within-a-string-in-sql

Answer 1

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