CODEWITHSUNDEEP
sqlsql-server
Akjell
Akjell

Reputation: 109

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

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?

Upvotes: 2

Views: 173

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82020

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

Upvotes: 6

Eray Balkanli
Eray Balkanli

Reputation: 8000

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

Upvotes: 0

Related Questions