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"
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)
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
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.
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
