select string before space - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select string before space

Author

Topic

eevans
Starting Member

48 Posts

Posted - 2009-07-07 : 15:52:19

Hello,
I would like to select the string occuring before the first space in in a larger string.

For example, if a column named AttentionLine contained 'John Smith', I want to return only 'John'.

Thanks!

Eric

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-07-07 : 16:06:50

[code]DECLARE @string varchar(20)
SELECT @string = 'John Smith'
SELECT SUBSTRING(@string, 1, CHARINDEX(' ', @string))[/code]

Some days you're the dog, and some days you're the fire hydrant.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-08 : 04:22:29

To omit last space

DECLARE @string varchar(20)
SELECT @string = 'John Smith'
SELECT SUBSTRING(@string, 1, CHARINDEX(' ', @string)-1)

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-08 : 07:08:11

But be careful! If the string contains no space that will throw an error.

Eample :


DECLARE @foo TABLE (
	[val] VARCHAR(255)
	)

INSERT @foo ([val])
      SELECT 'John Smith'
UNION SELECT 'NoSpaceHere'

SELECT SUBSTRING([val], 1, CHARINDEX(' ', [val]) -1) FROM @foo

Results in:


(2 row(s) affected)
Msg 536, Level 16, State 5, Line 9
Invalid length parameter passed to the SUBSTRING function.

To account for that you can use a CASE statement and a LIKE condition, or wrap the CHARINDEX. Like this


DECLARE @foo TABLE (
	[val] VARCHAR(255)
	)

INSERT @foo ([val])
      SELECT 'John Smith'
UNION SELECT 'NoSpaceHere'

SELECT SUBSTRING([val], 1, ISNULL(NULLIF(CHARINDEX(' ', [val]) -1, -1), LEN([val]))) FROM @foo

SELECT CASE WHEN [val] LIKE '% %' THEN SUBSTRING([val], 1, CHARINDEX(' ', [val]) -1) ELSE [val] END FROM @foo

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-08 : 10:15:04

Another way

select coalesce(parsename([val],2),parsename([val],1)) from
(
select replace([val],' ','.') as [val] from @foo
) as t

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-08 : 11:38:41

also


SELECT CASE WHEN CHARINDEX(' ',val)>0 THEN STUFF(val,CHARINDEX(' ',val),LEN(val)-CHARINDEX(' ',val)+1,'') ELSE val END FROM table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources