CODEWITHSUNDEEP
sqlsql-serversubstringcharindex
Smeghead
Smeghead

Reputation: 185

SQL Server - Substring part of a column in between specific text

CREATE TABLE test
(
    [message] NVARCHAR(100) NULL,
)
INSERT INTO test
    values
   ('Location set to: Loc1, Order: 1, item: shirt-red.large, company: 01, store: 01'),
   ('Location set to: Loc1, Order: 1, item: shirt-red.medium, company: 01, store: 01'), 
   ('Location set to: Loc2, Order: 220, item: shirt-blue.small, company: 01, store: 089'),
   ('Location set to: Loc2, Order: 220, item: shirt-blue.medium, company: 01, store: 089')

In SQL Server (2016), I'm trying to extract the full item string from the above column (e.g. 'shirt-red.large', 'shirt-blue.small' etc), using CHARINDEX to search for 'Item: ' and ', ', but I'm getting following error

"Invalid length parameter passed to the LEFT or SUBSTRING function"

Is this something to do with the ', ' being present multiple times in the string? What would be the best way to isolate the item as a result?

Upvotes: 1

Views: 244

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81960

Since you are on 2016, here's another option via string_split()

Example or dbFiddle

Select Item = ltrim(rtrim(replace(b.value,'item:','')))
 From  Test A
 Cross Apply string_split([message],',') b
 where charindex('item:',value)>0

Returns

Item
shirt-red.large
shirt-red.medium
shirt-blue.small
shirt-blue.medium

Note: Use Outer Apply if you want to see NULLs

Upvotes: 2

Paweł Dyl
Paweł Dyl

Reputation: 9143

Here is a solution:

SELECT SUBSTRING([message],
    CHARINDEX('item: ', [message])+6,
    CHARINDEX(',',message,CHARINDEX('item: ', [message]))-CHARINDEX('item: ', [message])-6)
FROM test

Upvotes: 0

S3S
S3S

Reputation: 25112

Not sure what your code is, but this should work

replace(substring([message],charindex('item: ',[message]),len([message]) - charindex(', company',[message])),',','')

Or without the item

left(substring([message],charindex('item: ',[message]) + 6,99),charindex(',',substring([message],charindex('item: ',[message]) + 6,99)) - 1)

Here it is in action:

declare @test TABLE ( [message] NVARCHAR(100) NULL)

INSERT INTO @test
values
('Location set to: Loc1, Order: 1, item: shirt-red.large, company: 01, store: 01'),
('Location set to: Loc1, Order: 1, item: shirt-red.medium, company: 01, store: 01'), 
('Location set to: Loc2, Order: 220, item: shirt-blue.small, company: 01, store: 089'),
('Location set to: Loc2, Order: 220, item: shirt-blue.medium, company: 01, store: 089')

select 
    withItem = replace(substring([message],charindex('item: ',[message]),len([message]) - charindex(', company',[message])),',','')
    ,withoutItem = left(substring([message],charindex('item: ',[message]) + 6,99),charindex(',',substring([message],charindex('item: ',[message]) + 6,99)) - 1)
from @test

Upvotes: 0

Related Questions