SQL Query for searching for a specific part of a string in a column

Question

I want to search for a specific part of a nvarchar text string in a column.

The text string is structured like this X#2016-06-17#7483631#2016-06-27#2#167890##920, it's split up by #. I want to find 6th text block, 167890 in this case.

Is there a simple way to do this?

Read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes! — Zohar Peled
– Zohar Peled, Commented Nov 6, 2019 at 15:01

John Cappelletti · Accepted Answer · 2019-11-06 14:57:38Z

6

Perhaps with a little XML

Example

Declare @YourTable table (SomeCol varchar(500))
Insert Into @YourTable values
('X#2016-06-17#7483631#2016-06-27#2#167890##920')

Select SomeCol
      ,Pos6 = cast('<x>' + replace(A.SomeCol,'#','</x><x>')+'</x>' as xml).value('/x[6]','varchar(50)')
 From  @YourTable A

Returns

SomeCol                                         Pos6
X#2016-06-17#7483631#2016-06-27#2#167890##920   167890

answered Nov 6, 2019 at 14:57

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eray Balkanli · Accepted Answer · 2019-11-06 14:57:30Z

Create a SPLIT function, returning each part with an index like below:

 CREATE FUNCTION [dbo].[StringSplit_WithIndex] (@str_in VARCHAR(8000),@separator VARCHAR(4) ) RETURNS @strtable TABLE (strval VARCHAR(8000),rn INT IDENTITY(1,1)) AS
 BEGIN
    DECLARE
    @Occurrences INT,
    @Counter INT,
    @tmpStr VARCHAR(8000)

    SET @Counter = 0
    IF SUBSTRING(@str_in,LEN(@str_in),1) <> @separator
        SET @str_in = @str_in + @separator
    SET @Occurrences = (DATALENGTH(REPLACE(@str_in,@separator,@separator+'#')) - DATALENGTH(@str_in))/ DATALENGTH(@separator)
    SET @tmpStr = @str_in
    WHILE @Counter <= @Occurrences
    BEGIN
        SET @Counter = @Counter + 1
        INSERT INTO @strtable
        VALUES ( SUBSTRING(@tmpStr,1,CHARINDEX(@separator,@tmpStr)-1))
        SET @tmpStr = SUBSTRING(@tmpStr,CHARINDEX(@separator,@tmpStr)+1,8000)
        IF DATALENGTH(@tmpStr) = 0
        BREAK
    END

    RETURN
 END

Then, all you need to use the "rn" field after splitting like:

 DECLARE @s VARCHAR(255) = 'X#2016-06-17#7483631#2016-06-27#2#167890##920'
 SELECT * 
 FROM [StringSplit_WithIndex](@s,'#') 
 WHERE rn=6

Looping for splitting strings is a terrible way to go about it. Of course most of the splitters out there don't have ordinal position. Fortunately Jeff Moden put together the DelimitedSplit8K function several years ago. Then it was improved with some new advancements in sql server by Eirikur Eiriksson. Try this one out instead of that while loop. sqlservercentral.com/articles/…

Collectives™ on Stack Overflow

SQL Query for searching for a specific part of a string in a column

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related