CODEWITHSUNDEEP
sqlsql-servert-sql
Randy
Randy

Reputation: 127

Trim Characters before and after from sql table column

From below sentence 

"Out of Stock  - N1452002 @ £22.56"

I want to extract 

N1452002

and show in a separate column, Can any one please help me with this. Your advise is highly apprecaited.

Upvotes: 1

Views: 605

Answers (4)

Cato
Cato

Reputation: 3701

in order to mitigate against the risk of the left most text containing @ or - characters, I came up with this. As long as you don't have '-' or @ to the right of where they are now, as long as the cash amount cannot have a - sign on it.

DECLARE @TEST AS nvarchar(200) = 'Out of Stock test @chars extra - etc  - N1452002 @ £22.56';


SELECT REVERSE(
            RTRIM(
                LEFT(
                        RIGHT(REVERSE(@TEST),LEN(@TEST) - CHARINDEX('@',REVERSE(@TEST))
                    ) , charindex('-', RIGHT(REVERSE(@TEST),LEN(@TEST) - CHARINDEX('@',REVERSE(@TEST))))-1)
                  )
                )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Assuming the string always has the same format and that the code is always 8 characters, you just need a substring function:

select substring(str, 17, 8)

That is my simplest interpretation of what you want.

If you want to find the first "code" in the string -- and it follows the format you specified, then this should work:

select left(stuff(str, 1, patindex('% [A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', str
                 ), ''), 8
           )

Upvotes: 1

Pawel Czapski
Pawel Czapski

Reputation: 1864

You can play with SUBSTRING and CHARINDEX like below, it will work if always before your required string is - and space, and after your required string space and @

declare @string nvarchar(50) = 'Out of Stock  - N1452002 @ £22.56'
select substring(@string, CHARINDEX('-', @string) + 2, 
                    CHARINDEX('@', @string)-3 - CHARINDEX('-', @string))

Upvotes: 0

abbgrade
abbgrade

Reputation: 578

with SQL Server 2016 there was string_split introduced:

select value from string_split('Out of Stock  - N1452002 @ £22.56', ' ') where left(value, 1) = 'N'

Upvotes: 0

Related Questions