removing parts of an existing field

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)

removing parts of an existing field

Author

Topic

ams006
Yak Posting Veteran

55 Posts

Posted - 2012-05-25 : 07:57:39

Hi,

I have a string field which consists of xxxxxx - xxxxxx.
I would like to extract just the first xxxxxx and not the ' - xxxxxx'
(space hyphen space xxxxxx).

Any help will be greatly appreciated.

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-05-25 : 08:06:39

declare @str varchar(20) = 'xxxxxx - xxxxxx'

SELECT LEFT(@str,PATINDEX('% - %',@str)-1)

Jim

Everyday I learn something that somebody else already knew

ams006
Yak Posting Veteran

55 Posts

Posted - 2012-05-25 : 10:01:37

You're a star....worked like a charm :)

ams006
Yak Posting Veteran

55 Posts

Posted - 2012-05-25 : 10:01:57

sorry, forgot to type 'Thank you'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-25 : 10:37:19

if its just two parts always this will also work


SELECT PARSENAME(REPLACE(@str,' - ','.'),2) AS Required_Val FROM Table

or even this

SELECT STUFF(@str,CHARINDEX(' - ',@Str),LEN(@Str),'') FROM table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources