Search code examples
sqlsql-serverstringsubstring

SQL SUBSTRING to extract all parts of a column

I am trying to extract values from a column using the SQL SUBSTRING method. The values from the column are in this format: VI_{13446F4A-DAC2-44AD-BDAB-86E6F2546DE6}_20220122T090159. I am trying to obtain all substrings delimited by the _ character. I have managed to extract the first part, but I did not find a way for the others using:

DECLARE @Str nchar(200);
SET @Str = 'VI_{13446F4A-DAC2-44AD-BDAB-86E6F2546DE6}_20220122T090159'

SELECT SUBSTRING(@Str, 1, CHARINDEX('_', @Str) -1) AS F1,
       SUBSTRING(@Str, CHARINDEX('_', @Str)+1, LEN(@Str)) AS F2

The code above gives me F1 correct, but F2 is incorrect:

F1 = VI
F2 = {13446F4A-DAC2-44AD-BDAB-86E6F2546DE6}_20220122T090159

How can I get the other two parts?

Thanks!

Solution

  • If there is no inline ., I think you could use PARSENAME

    DECLARE @original VARCHAR(255) = 'VI_{13446F4A-DAC2-44AD-BDAB-86E6F2546DE6}_20220122T090159'
DECLARE @new VARCHAR(255) = REPLACE(@original,'_','.')

SELECT PARSENAME(@new,3) as F1,
       PARSENAME(@new,2) as F2,
       PARSENAME(@new,1) as F3