How do I strip a field of special characters

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 2000 Forums

Transact-SQL (2000)

How do I strip a field of special characters

Author

Topic

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-10-31 : 21:02:36

Hi,

I have a Col :-

PartNo

!!@KU&7*&

I need an outpu like so :-

KU7

How can I acheive in a select statement.

Thanx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-01 : 02:28:57

will the format be always consistent? like first three special characters then two alphabets,&, then a number follows be again special characters? Also will specila charcters be always !,@,&,*?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-01 : 02:38:43

any ways if its consistent use this

SUBSTRING(field,4,2) + SUNSTRING(field,7,1)

or use REPLACE

REPLACE(REPLACE(REPLACE(REPLACE(field,'!',''),'@',''),'&',''),'*','')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-01 : 04:55:25

or change '[0-9]' to '[0-9a-zA-Z]' here
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx

Madhivanan

Failing to plan is Planning to fail

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-11-01 : 08:15:31

Visakh16 and Madhi,

No the format will not be same.

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-11-01 : 08:28:07

Madhi,

Your solution is quite close to what I want. However the method available in the link extracts only numbers. I am sure you can go a step further and include alphabets too.

By the way the inner select uses "master..spt_values". What is this Sp. Could amplify.

Thanx

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-11-01 : 08:34:09

Visakh16,

Your solution seems to be working fine. The only thing is I will have use the Replace function nested 33 levels deep. But that's Okay.

Thanx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-01 : 08:41:26

quote:
Originally posted by mayoorsubbu

Madhi,

Your solution is quite close to what I want. However the method available in the link extracts only numbers. I am sure you can go a step further and include alphabets too.

By the way the inner select uses "master..spt_values". What is this Sp. Could amplify.

Thanx

You didnt do what I asked you to do

Declare @s varchar(100),@result varchar(100)set @s='!!@KU&7*&' 
set @result=''
select          
	@result=@result+case when number like '[0-9a-zA-Z]' then number else '' end 
from         
( 
	select substring(@s,number,1) as number from 
	    (
			select number from master..spt_values where type='p' and number between 1 and len(@s)             
		) as t         
) as t 
select @result as only_numbers

Madhivanan

Failing to plan is Planning to fail

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-11-02 : 11:50:35

Madhi,

Your solution is damn good. But I don't think this can be used in a select statement or maybe there is way that I am not aware. Could you help.

Thanx

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-11-02 : 11:53:10

Madhi,

Refer my previous post. Could you tell me what does the undermentioned Sp do

"master..spt_values".

Thanx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-02 : 11:57:06

make a function using Madhis caode and call it in your select statement/. something like


CREATE FUNCTION GetPartNo
@s varchar(100)
RETURNS varchar 
AS
begin
DECLARE @result varchar(100)

set @result=''
select          
	@result=@result+case when number like '[0-9a-zA-Z]' then number else '' end 
from         
( 
	select substring(@s,number,1) as number from 
	    (
			select number from master..spt_values where type='p' and number between 1 and len(@s)             
		) as t         
) as t 
return @result
end

then call it in your select

SELECT dbo.GetPartNo(PartNo) FROM YourTable

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-03 : 01:30:16

quote:
Originally posted by mayoorsubbu

Madhi,

Refer my previous post. Could you tell me what does the undermentioned Sp do

"master..spt_values".

Thanx

It is a system table which I use it as number table

select number from master..spt_values
where type='p' order by number

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources