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

Question

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?

That error feels like you used your parameters in the wrong order. Could you post your query? — George Menoutis
– George Menoutis, Commented Aug 24, 2018 at 13:36
The main mistake is storing what looks like it should be structured data as plain text. Even if you have different attributes, for example, an XML format would be far more helpful and get you ~95% towards having a simple solution here. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Aug 24, 2018 at 13:42

John Cappelletti · Accepted Answer · 2018-08-24 13:56:31Z

2

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

edited Aug 24, 2018 at 13:56

answered Aug 24, 2018 at 13:50

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

S3S · Accepted Answer · 2018-08-24 13:43:28Z

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

Paweł Dyl · Accepted Answer · 2018-08-24 13:53:09Z

0

Here is a solution:

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

answered Aug 24, 2018 at 13:53

Paweł Dyl

9,1831 gold badge13 silver badges28 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related