sql server get value between spaces in one column

Question

I need help in getting the value in between spaces in one column. For the requirement below, I would need the value after the first space.

I have a column with multiple rows:

Original_Column

Microsoft SQL Server
Oracle DB Server
Delete From Table Name
SELECT as123 987

The expected output is

Expected_Output

SQL
DB
From 
as123

Please share the query you have tried thus far. You will need SubString and CharIndex functions to get the result. — danish
– danish, Commented Nov 17, 2014 at 4:14
Find the first space, find the next space (starting character after initial space), Substring from those two index points. (CHARINDEX) — Adam Wenger
– Adam Wenger, Commented Nov 17, 2014 at 4:14

Gordon Linoff · Accepted Answer · 2014-11-17 04:25:34Z

5

One trick for this type of problem is to use parsename(). I think the following does what you want, assuming there are no periods in the names:

select parsename(replace(val, ' ', '.'), 2)

Here is an example.

EDIT:

Sgeddes is correct. If you consistently want the second name and can have three or four parts, then reverse() can be used:

select reverse(parsename(replace(reverse(val), ' ', '.'), 2))

(It seems that one of the values does have four parts; I originally read it as "Delete From TableName".)

answered Nov 17, 2014 at 4:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

+1, I like this, but I think it should be used with reverse: sqlfiddle.com/#!3/703ce/1

Pரதீப் · Accepted Answer · 2014-11-17 05:31:40Z

0

Try this. Use Substring, Charindex and Left string function to get the value.

select left(substring(Col,charindex(' ',Col)+1,len(Col)),charindex(' ',substring(Col,charindex(' ',Col)+1,len(Col))))

answered Nov 17, 2014 at 5:13

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges