Remove text from value - 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)

Remove text from value

Author

Topic

edusqluser
Starting Member

15 Posts

Posted - 2014-05-09 : 17:48:53

I need to update the values in a table from:

7F110-0100-010-00-PUBLISHING/GENERAL

to

7F110-0100-010-00

How to remove the text to the RIGHT of the numeric values

THX, EDUSQLUSER

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-09 : 17:51:14

Will it always be 17 characters that you want to keep?

select LEFT(column1, 17)
from t1

If you want to update the data, then: update t1 set column1 = LEFT(column1, 17)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-05-12 : 07:19:12

if length is varying you can use this


select LEFT(column1, PATINDEX('%-[A-Za-z]%',Column1 + '-A')-1)
from t1

or 
update t1 set column1 = LEFT(column1, PATINDEX('%-[A-Za-z]%',Column1 + '-A')-1)

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

edusqluser
Starting Member

15 Posts

Posted - 2014-05-12 : 18:39:18

Awesome! I appreciate it!

THX, EDUSQLUSER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources