Calvs Calvs - 1 month ago 6
SQL Question

Extracting Specific words from a dynamic string source

Hello I'm new to the Stack overflow though I've been using this site to search for answers for years. So I have this problem to extract various and specific details from a very LONG string. Problem is, the Strings are not static and doesn't have a fixed index so I can't use the substring function. Example of a string output is like this.

Sample 1:
1 AJ 552 O 9/28/2016 0:0:0 6 PSI KTL 0 GK 2 9/28/2016 7:20:0 9/28/2016 8:35:0 O20YEARS 32.2400

Sample 2:
1 AJ 2552 O 10/8/2016 0:0:0 6 PSI KTL 0 GK 2 10/28/2016 7:0:0 9/28/2016 8:5:0 O20YEARS 32.2400


As you can see, indexes of the strings are adjusted accordingly thus I can't rely on a fixed substring function.

I'm fairly new to SQL and would like your advice on how should I approach this.

So far, methods i tried are only substrings.

UPDATE:

Sorry, I forgot to mention that I'm not extracting data base on any characters or string. I'm extracting an exact part from this string. For instance,

Sample 1:
1 AJ 552 O 9/28/2016 0:0:0 6 PSI KTL 0 GK 2 9/28/2016 7:20:0 9/28/2016 8:35:0 O20YEARS 32.2400


I need to extract the First Date which is "9/28/2016" and the "PSI KTL"

So technically, I need to extract a certain nth word from a string.

Right now, I'm trying to use the space as a delimiter and assigning each word into temp table but it seems it is not Working as of now.

Thanks!

Answer

Try this:

CREATE FUNCTION GetWordBasedOnIndex(@text varchar(max),@index int)
RETURNS varchar(50)
AS
BEGIN
    DECLARE @RET VARCHAR(50),@counter int
    SET @text = LTRIM(RTRIM(@text))
    SET @counter = 1
    IF @counter < @index
    BEGIN
            SET @text = LTRIM(RTRIM(SUBSTRING(@text, CHARINDEX(' ',@text,1), LEN(@text) - CHARINDEX(' ',@text,1)+1)))
            SET @text = dbo.GetWordBasedOnIndex(@text,@index-1)
    END
    IF CHARINDEX(' ',@text,1)=0 SET  @RET = @text
    ELSE SET  @RET = SUBSTRING(@text,@counter,CHARINDEX(' ',@text,1))
    RETURN @RET
END

declare @string nvarchar(max)
set @string = '1 AJ 552 O 9/28/2016 0:0:0 6 PSI KTL 0 GK 2 9/28/2016 7:20:0 9/28/2016 8:35:0 O20YEARS 32.2400';
SELECT DBO.GetWordBasedOnIndex(@string, 5)
Comments