SQL Server 2012 string functions-CodePudding

I have a field that can vary in length of the format CxxRyyy where x and y are numeric. I want to choose xx and yyy. For instance, if the field value is C1R12, then I want to get 1 and 12. if I use substring and charindex then I have to use a length, but I would like to use a position like

SUBSTRING(WPLocationNew, CHARINDEX('C',WPLocationNew,1) 1,  CHARINDEX('R',WPLocationNew,1)-1)

SUBSTRING(WPLocationNew, CHARINDEX('C',WPLocationNew,1) 1,  LEN(WPLocationNew) - CHARINDEX('R',WPLocationNew,1))

to get x, but I know that doesn't work. I feel like there is a fairly simple solution, but I am not coming up with it yet. Any suggestions

CodePudding user response：

It appears that you are attempting to parse an Excel cell reference. Those are predictably structured or I wouldn't suggest such an embarrassing hack as this.

Basically, take advantage of the fact that a try_cast in SQL ignores spaces when converting strings to numbers.

declare @val as varchar(20) = 'C1R12'
declare @newval as varchar(20)
declare @c as smallint
declare @r as smallint

--replace the C with 5 spaces
set @newval = replace(@val,'C','     ')

--replace the R with 5 spaces
set @newval = replace(@newval,'R','     ')

--take a look at the intermediate result, which is '     1     14'
select @newval

set @c = try_cast(left(@newval,11) as smallint)
set @r = try_cast(right(@newval,6) as smallint)

--take a look at the results... two smallint, 1 and 14
select @c, @r

That can all be accomplished in one line for each element (a line for column and a line for row) but I wanted you to be able to understand what was happening so this example goes through the steps individually.

CodePudding user response：

If these are cell references and will always be in the form C{1-5 digits}R{1-5 digits} you can do this:

DECLARE @t TABLE(Original varchar(32));

INSERT @t(Original) VALUES ('C14R4535'),('C1R12'),('C57R123');

;WITH src AS
(
  SELECT Original, 
    c = REPLACE(REPLACE(Original,'C',''),'R','.') 
  FROM @t
)
SELECT Original, 
       C = PARSENAME(c,2), 
       R = PARSENAME(c,1) 
FROM src;

Output

Original C R

C14R4535 14 4535

C1R12 1 12

C57R123 57 123

Original	C	R
C14R4535	14	4535
C1R12	1	12
C57R123	57	123

Example db<>fiddle

If you need to protect against other formats, you can add

  FROM @t
  WHERE Original LIKE 'C%[0-9]%R%[0-9]%' 
    AND PATINDEX('%[^C^R^0-9]%', Original) = 0

Updated db<>fiddle

CodePudding user response：

Here's yet another way:

declare @val as varchar(20) = 'C12R345'
declare @c   as varchar(5)
declare @r   as varchar(5)

set @c = SUBSTRING(@val, patindex('C%', @val) 1,(patindex('%R%', @val)-1)-patindex('C%', @val) ) 
set @r = SUBSTRING(@val, patindex('%R%', @val) 1, LEN(@val) -patindex('%R%', @val))

select cast(@c as int) as 'C', cast(@r as int) as 'R'

dbfiddle

CodePudding user response：

There are lots of different ways to approach string parsing. Here's just one possible idea:

declare @s varchar(10) = 'C01R002';
select
    rtrim( left(replace(stuff(@s, 1, 1, ''), 'R', '          '), 10)) as c,
    ltrim(right(replace(substring(@s, 2, 10), 'R', '          '), 10)) as r

Strip out the 'C' and then replace the 'R' with enough spaces so that the left and right sides can be extracted using a fixed length and then easily trimmed back.

stuff() and substring() as used above are just different ways accomplish exactly the same thing. One advantage here is that it does use fairly portable string functions and it's conceivable that this is somewhat faster. This is also done inline and without multiple steps.