Substring string between underscore - SQL Server 2008

Question

I have a string stored in my SQL Server 2008 and I want to extract the value between the underscores. I have a working code but it creates a problem when the length of the string changes. Could you please let me know a better way to do this?

Sample string is

'AAAA_BBBBUSEmail000000001_1234567_Normal_ABC_US'

and I want to extract

'1234567'

which will always between second and third last underscore.

I am using this code:

substring(right(sample_table.file_name, 21), 1, 7)

where sample_table is the table name and file_name is the column name

Output should be 1234567

Just as a note: SQL Server 2008 and 2008 R2 are **out of extended support** by now - https://www.red-gate.com/simple-talk/sql/database-administration/the-end-of-sql-server-2008-and-2008-r2-extended-support/ - time to upgrade! — marc_s, Jul 12 '19 at 15:12

score 3 · Answer 1 · answered Jul 12 '19 at 13:47

3

Assuming you want the third position, perhaps a little XML

Example

Declare @YourTable table (ID int,file_name varchar(max))
Insert Into @YourTable values
(1,'AAAA_BBBBUSEmail000000001_1234567_Normal_ABC_US')

Select ID
      ,SomeValue = Cast('<x>' + replace(file_name,'_','</x><x>')+'</x>' as xml).value('/x[3]','varchar(max)')
 From @YourTable

Returns

ID  SomeValue
1   1234567

answered Jul 12 '19 at 13:47

John Cappelletti

79,615
7
44
66

@PrayasBhatnagar Accepting another answer is perfectly fine. Seriously, I have not issue with that, but why on earth would you accept a LOOPING parse/split function. I'm shocked that people are still posting those when there are set-based alternatives available. For the sake of your technical growth, take a harder look a the function that Kenny linked to. Respectfully, John – John Cappelletti Jul 15 '19 at 16:23
1

Yes John, I did look at Kenny's answer but haven't tried it yet. Not sure if it will work with my requirement or not. Yours and Pierre answers have supported my requirement and hence accepted the answer. I understand what you are saying and have taken it into good spirit but the only reason why I didn't negate Pierre answer and haven't accepted Kenny's answer yet us because one supports my requirement and the other (better solution) have not been tested yet. That's it. Peace – Prayas Bhatnagar Jul 15 '19 at 16:29
1

@PrayasBhatnagar Years ago, I had a similar looping function. I was amazed at the performance gains when I switched to a set-based approach. A performant function is a great enabler. Nuff said ... :) – John Cappelletti Jul 15 '19 at 16:44

score 2 · Answer 2 · answered Jul 12 '19 at 14:41

One way is to use a string splitter like the DelimitedSplit8K function from http://www.sqlservercentral.com/articles/Tally+Table/72993/ . If all you want is the 3rd value then below is an example of how you would accomplish it.

CREATE TABLE #TableA (ID Int, DelimitedString Varchar(100));

INSERT #TableA(ID, DelimitedString)VALUES(1, 'AAAA_BBBBUSEmail000000001_1234567_Normal_ABC_US'), (2, 'ZZZZ_YYYYUSEmail000000002_9876543_Abnormal_XYZ_US');

SELECT
    a.ID,
    split.Item AS ThirdValue
FROM
    #TableA a
CROSS APPLY
    master.dbo.DelimitedSplit8K(a.DelimitedString, '_') split
WHERE
    split.ItemNumber = 3

DROP TABLE #TableA;

Returns

ID  ThirdValue
--  ----------
1   1234567
2   9876543

If you would want more values from the string then this method can be coupled with PIVOT, assuming that there is a static number of values, to turn the list of values into columns for easy use. Here is an example using PIVOT as well.

CREATE TABLE #TableA (ID Int, DelimitedString Varchar(100));

INSERT
    #TableA(ID, DelimitedString)
VALUES
    (1, 'AAAA_BBBBUSEmail000000001_1234567_Normal_ABC_US'),
    (2, 'ZZZZ_YYYYUSEmail000000002_9876543_Abnormal_XYZ_US');

SELECT
    pvt.ID,
    pvt.[1] AS FirstValue,
    pvt.[2] AS SecondValue,
    pvt.[3] AS ThirdValue,
    pvt.[4] AS FourthValue,
    pvt.[5] AS FifthValue,
    pvt.[6] AS SixthValue
FROM
    (
    SELECT
        a.ID,
        a.DelimitedString,
        split.ItemNumber,
        split.Item
    FROM
        #TableA a
    CROSS APPLY master.dbo.DelimitedSplit8K(a.DelimitedString, '_') split
    ) list
PIVOT(MAX(Item)
FOR ItemNumber IN([1], [2], [3], [4], [5], [6])
    ) pvt;

DROP TABLE #TableA;

Returns

ID  FirstValue  SecondValue           ThirdValue  FourthValue  FifthValue  SixthValue
--  ----------  --------------------  ----------  -----------  ----------  ----------
1   AAAA        BBBBUSEmail000000001  1234567     Normal       ABC         US
2   ZZZZ        YYYYUSEmail000000002  9876543     Abnormal     XYZ         US

score 1 · Accepted Answer · answered Jul 12 '19 at 14:01

Ok here an solution that should work also on SQL Server 2008

First create an function:

CREATE FUNCTION dbo.StringSplit(@string nvarchar(MAX), @separator nvarchar(MAX))
RETURNS
    @list TABLE ([index] int, [value] nvarchar(MAX))
AS
BEGIN
    DECLARE @value NVARCHAR(max)
    DECLARE @pos INT
    DECLARE @index INT

    SET @index = 1

    WHILE CHARINDEX(@separator, @string) > 0
    BEGIN
        SELECT @pos  = CHARINDEX(@separator, @string)  
        SELECT @value = SUBSTRING(@string, 1, @pos - 1)
        INSERT INTO @list SELECT @index, @value

        SELECT @string = SUBSTRING(@string, @pos + LEN(@separator), LEN(@string) - @pos)
        SET @index = @index + 1
    END

    INSERT INTO @list
    SELECT @index, @string

    RETURN
END

Then split the values and select the right one

SELECT * 
FROM dbo.StringSplit('AAAA_BBBBUSEmail000000001_1234567_Normal_ABC_US', '_') 
WHERE [index] = 3

Returns

index       value
----------- ---------
3           1234567

Thanks. This also works but i am going with the approach shared by @John — Prayas Bhatnagar, Jul 12 '19 at 15:00

Substring string between underscore - SQL Server 2008

3 Answers3