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