Derive Column Remove Characters to Left

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

SSIS and Import/Export (2005)

Derive Column Remove Characters to Left

Author

Topic

Brittney10
Posting Yak Master

154 Posts

Posted - 2013-05-16 : 11:48:16

I need to create a derived column that removes the characters to the left of an asterisk.

Example:

5678*12345 would be 12345

Thanks for you help in advance!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-16 : 12:17:53

[code]STUFF(col,1,CHARINDEX('*',col+'*'),'')[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 00:10:57

SELECT PARSENAME(REPLACE(col,'*','.'),1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-17 : 08:11:16

quote:
Originally posted by visakh16

SELECT PARSENAME(REPLACE(col,'*','.'),1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

PARSENAME is less reliable than th emethod I posted. Both the examples shown below will return inocrrect results if you use parsename

SELECT PARSENAME(REPLACE('abc*x.yz','*','.'),1)
SELECT PARSENAME(REPLACE('abc[d*e]xyz','*','.'),1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources