CODEWITHSUNDEEP
sql-serverstring
MartinL
MartinL

Reputation: 45

SQL Select all words in a string from the third word

My data set is changing and now includes two additional words at the start of my customer name field, I need to clean this data up before moving it to my main customer table.

What I need to be able to do is keep only the words after the second space in a select statement.

Can anyone suggest a way to do this

i.e. "ZENDUSER ABCABC S ROCCO AL PORTO" needs to be returned as "S ROCCO AL PORTO"

Upvotes: 1

Views: 703

Answers (4)

hkravitz
hkravitz

Reputation: 1385

If you'd like your trimming to be more dynamic i.e starting from the 5th word etc, you can use the following code snippet. I would have encapsulate this in an inline function for additional capabilities

    DECLARE @Sentence NVARCHAR(200) = 'ZENDUSER ABCABC S ROCCO AL PORTO'
    DECLARE @Del NVARCHAR(2)= ' '
    DECLARE @WordStart INT = 5

    ;WITH Nums (n) as 
        (
        SELECT TOP (LEN(@Sentence)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) a(n)
        CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
        CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
        CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n)
        )
    , Main as 
    (
    SELECT N, x.val , Ind , CASE WHEN n = 1 THEN 1  ELSE SUM(Ind) OVER (ORDER BY n) + 1  END Pos
    FROM Nums
        CROSS APPLY 
            (
            VALUES (SUBSTRING(@Sentence,n,1),
            CASE WHEN SUBSTRING(@Sentence,n,1) = @Del THEN 1 ELSE 0 END) 

            ) x(val, Ind)
    )
    , Combine (StrOut) as 
    (
    SELECT LTRIM(RTRIM(STUFF(
            CAST((SELECT ''+ val
            FROM Main
            WHERE Pos >= @WordStart
            FOR XML PATH (''),TYPE) AS NVARCHAR(MAX)),1,0,'')
            )))
     SELECT StrOut 
     FROM Combine

UPDATE: creating a function

    CREATE FUNCTION dbo.SentenceSplitter 
    (
    @Sentence NVARCHAR(2000),
    @WordStart INT,
    @Del NVARCHAR(2) = ' ' 
    )
    RETURNS TABLE AS RETURN

        WITH Nums (n) as 
                (
                SELECT TOP (LEN(@Sentence)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
                FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0)) a(n)
                CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
                CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
                CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d(n)
                )
            , Main as 
            (
            SELECT N, x.val , Ind , CASE WHEN n = 1 THEN 1  ELSE SUM(Ind) OVER (ORDER BY n) + 1  END Pos
            FROM Nums
                CROSS APPLY 
                    (
                    VALUES (SUBSTRING(@Sentence,n,1),
                    CASE WHEN SUBSTRING(@Sentence,n,1) = @Del THEN 1 ELSE 0 END) 

                    ) x(val, Ind)
            )
            , Combine (StrOut) as 
            (
            SELECT LTRIM(RTRIM(STUFF(
                    CAST((SELECT ''+ val
                    FROM Main
                    WHERE Pos >= @WordStart
                    FOR XML PATH (''),TYPE) AS NVARCHAR(MAX)),1,0,'')
                    )))
             SELECT StrOut 
             FROM Combine

Use case:

SELECT StrOut
FROM dbo.SentenceSplitter ('ZENDUSER ABCABC S ROCCO AL PORTO', 5, ' ')

will result:

StrOut
AL PORTO

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37460

Try this:

select substring(MyColumn, CHARINDEX(MyColumn, ' ', CHARINDEX(MyColumn, ' ', 1) + 1) + 1, Len(MyColumn)) from MyTable

I know, that this is very similair to MJH answer, but additionally, I take Len(MyColumn) in substring method, so we are sure that we include all characters after second space. The other answer takes only 200 characters.

Upvotes: 0

McNets
McNets

Reputation: 10817

DECLARE @cust NVARCHAR(MAX);
SET @cust = N'ZENDUSER ABCABC S ROCCO AL PORTO';

SELECT SUBSTRING(@cust, CHARINDEX(' ', @cust, CHARINDEX(' ', @cust, 0) + 1) + 1,
                  LEN(@cust) - CHARINDEX(' ', @cust, CHARINDEX(' ', @cust, 0) + 1));
 GO


| (No column name) |
| :--------------- |
| S ROCCO AL PORTO |

dbfiddle here

Upvotes: 0

MJH
MJH

Reputation: 1750

You can use CHARINDEX and SUBSTRING to do this:

declare @a varchar(200)

set @a = 'ZENDUSER ABCABC S ROCCO AL PORTO'

select  @a, substring(@a, charindex(' ', @a, charindex(' ', @a, 1) + 1) + 1, 200)

Upvotes: 2

Related Questions