removed characters - 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 2012 Forums

Transact-SQL (2012)

removed characters

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2013-10-08 : 08:38:39

hi,
how do i remove all the irreular signs
like
#
-OLD
~
*

my column:
DB54-0006#2-OLD
N427722#1-OLD
16GB0141#7-OLD
M02025006A~1

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2013-10-08 : 09:05:54

SELECT REPLACE(REPLACE(REPLACE(REPLACE(field,'#',''),'-OLD',''),'~',''),'*','') FROM Table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-08 : 09:18:19

See if this helps http://beyondrelational.com/modules/2/blogs/70/posts/10864/removing-unwanted-characters.aspx

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-08 : 09:38:50

quote:
Originally posted by inbs

hi,
how do i remove all the irreular signs
like
#
-OLD
~
*

my column:
DB54-0006#2-OLD
N427722#1-OLD
16GB0141#7-OLD
M02025006A~1

do you've a full list or do you need to remove all special characters?

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

inbs
Aged Yak Warrior

860 Posts

Posted - 2013-10-08 : 13:11:54

the REPLACE function it is good but not enough.
see the next example
i have this value 14GC7310#10~01
and i want to get 14GC7310

140713#01 ---> 140713
N485083#01--->N485083
N485083#01-OLD-->N485083

i want to clean all characters after the first irrgular character

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-08 : 13:23:39

quote:
Originally posted by inbs

the REPLACE function it is good but not enough.
see the next example
i have this value 14GC7310#10~01
and i want to get 14GC7310

140713#01 ---> 140713
N485083#01--->N485083
N485083#01-OLD-->N485083

i want to clean all characters after the first irrgular character


SELECT LEFT(Field,CASE WHEN PATINDEX('%[^0-9A-Za-z]%',Field)>0 THEN PATINDEX('%[^0-9A-Za-z]%',Field)-1 ELSE LEN(Field) END)
FROM Table

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources