CODEWITHSUNDEEP
sql-serverstringt-sqlsubstring
Michael
Michael

Reputation: 2657

T-SQL Extract string between two known characters

I'm using MS SQL Server 2014. I have a table that contains strings (assigned to field CAR) like the below:

ORANGE 1 FORD FIESTA;PETROL
RED 24 FORD MUSTANG; PETROL
BLACK 2 NISSAN JUKE; DIESEL

However, I need to extract everything from the 2nd space ' ' to the first semi colon ; to look like the below:

FORD FIESTA
FORD MUSTANG
NISSAN JUKE

This is my SQL:

SELECT SUBSTRING(CAR, CHARINDEX(' ', CAR) , CHARINDEX(';',CAR) - CHARINDEX(' ', CAR) + Len(';'))
FROM tblStock;

However, this gives me the below:

 1 FORD FIESTA;
24 FORD MUSTANG;
2 NISSAN JUKE;

Where am I going wrong?

Upvotes: 1

Views: 752

Answers (2)

sacse
sacse

Reputation: 3744

You are picking everything from first space to the first semicolon.

You can pick in the reversed string from the "first semicolon" to first "space followed by a number" (assuming every record has the number in it) and then finally reverse the string returned.

SELECT reverse(trim(substring(reverse(car), charindex(';', reverse(car), 1)+1, 
                 PATINDEX('% [0-9]%', REVERSE(car)) - charindex(';', reverse(car), 1)))) Final

Please find the db<>fiddle here.

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81930

Just another option

Example

Declare @YourTable Table ([Car] varchar(50))
Insert Into @YourTable Values 
 ('ORANGE 1 FORD FIESTA;PETROL')
,('RED 24 FORD MUSTANG; PETROL')
,('BLACK 2 NISSAN JUKE; DIESEL')
 
Select A.*
      ,NewValue = stuff(left(Car,charindex(';',Car+';')-1),1,patindex('%[0-9] %',Car+'0'),'')
 From  @YourTable A

Returns

Car                            NewValue
ORANGE 1 FORD FIESTA;PETROL    FORD FIESTA
RED 24 FORD MUSTANG; PETROL    FORD MUSTANG
BLACK 2 NISSAN JUKE; DIESEL    NISSAN JUKE

Upvotes: 2

Related Questions