SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 removed characters
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

inbs
Aged Yak Warrior

836 Posts

Posted - 10/08/2013 :  08:38:39  Show Profile  Reply with Quote
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

United Kingdom
3608 Posts

Posted - 10/08/2013 :  09:05:54  Show Profile  Reply with Quote
SELECT REPLACE(REPLACE(REPLACE(REPLACE(field,'#',''),'-OLD',''),'~',''),'*','') FROM Table
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22765 Posts

Posted - 10/08/2013 :  09:18:19  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/08/2013 :  09:38:50  Show Profile  Reply with Quote
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
Go to Top of Page

inbs
Aged Yak Warrior

836 Posts

Posted - 10/08/2013 :  13:11:54  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/08/2013 :  13:23:39  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000