Parse a stirng in sql - 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 2000 Forums

Transact-SQL (2000)

Parse a stirng in sql

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-01-04 : 12:52:12

I have a colum in sql and I need to remove the middle inital which appears at the end of the string and there is a space between the last name and the middle inital. Braeden M How can I get it so that on the Braeden is written to the column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-04 : 12:59:11

SELECT
CASE
WHEN CHARINDEX(' ',Column,LEN(Column)-1)>0 THEN LEFT(Column,CHARINDEX(' ',Column,LEN(Column)-1)-1)
ELSE Column
END
FROM TABLE

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-01-04 : 14:47:01

this gives me the error subquery returned more than 1 value. Thanks

update upload set lname = (SELECT
CASE
WHEN CHARINDEX(' ',lname,LEN(lname)-1)>0 THEN LEFT(lname,CHARINDEX(' ',lname,LEN(lname)-1)-1)
ELSE lname
END
FROM upload)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-01-04 : 15:06:10

There's no reason to use it as a subquery. Try this:

update upload
set lname = CASE WHEN CHARINDEX(' ',lname,LEN(lname)-1)>0 THEN LEFT(lname,CHARINDEX(' ',lname,LEN(lname)-1)-1)
ELSE lname
END

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources