Stripping alpha numeric charecters

Question

Stripping alpha numeric charecters

am-244616

SSCarpal Tunnel

Points: 4936
More actions
January 2, 2009 at 7:39 pm

#217163

Guys,
I am trying to update a name column by stripping out alpha numeric charecters below are the steps I am doing, but I am
getting the error in the last step
CREATE FUNCTION dbo.GetCharacters(@myString varchar(500), @validChars varchar(100))
RETURNS varchar(500) AS
BEGIN
While @myString like '%[^' + @validChars + ']%'
Select @myString = replace(@myString,substring(@myString,patindex('%[^' + @validChars + ']%',@myString),1),'')
Return @myString
END
Go
create table test14 (fname varchar(10), fname_bak varchar(10))
insert into test14 (fname) values ('ja.')
insert into test14 (fname) values (',ja,')
insert into test14 (fname) values ('ja ')
alter table test14 add id int identity (1, 1) not null
update test14
set fname_bak = (select dbo.GetCharacters (t.fname, 'a-z') from test14 t)
from test14 inner join t on t.id = test14.id
go
I am failing at the last step which is update
'Msg 208, Level 16, State 1, Line 1
Invalid object name 't'.'
Any suggestion and inputs to update fname_bak column would help
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29423 More actions · Answer 1

am (1/2/2009)
Guys,
update test14
set fname_bak = (select dbo.GetCharacters (t.fname, 'a-z') from test14 t)
from test14 inner join t on t.id = test14.id
go
I am failing at the last step which is update
'Msg 208, Level 16, State 1, Line 1
Invalid object name 't'.'
Any suggestion and inputs to update fname_bak column would help
Thanks

Why do you need to perform a join? a simple update query would suffice.

--================================

update test14

set fname_bak = dbo.GetCharacters (fname, 'a-z')

go

--================================

Pradeep Singh

am-244616 SSCarpal Tunnel Points: 4936 More actions · Answer 2

this statement will error out since you cannot pass function in the update statement. Is there anyway to accomplish this.

ps. One Orange Chip Points: 29423 More actions · Answer 3

am (1/2/2009)
this statement will error out since you cannot pass function in the update statement. Is there anyway to accomplish this.

Why cant i use function with UPDATE?

I tested that statement before posting.

update test14

set fname_bak = dbo.GetCharacters (fname, 'a-z')

go

Output-

--------

(3 row(s) affected)

--------------------------

select * from test14

Output-

----------

fname fname_bak id

---------- ---------- -----------

ja. ja 1

,ja, ja 2

ja ja 3

(3 row(s) affected)

Pradeep Singh

GSquared SSC Guru Points: 260824 More actions · Answer 4

By the way, if you want a faster string cleaning function, try this:

create function StringStrip

(@String_in varchar(1000),

@IncludeChars_in varchar(100))

returns varchar(1000)

as

begin

declare @String_out varchar(1000);

;with

S1 (Number, S1_sub) as -- breaks up the string into a column of characters

(select Number, substring(@String_in, number, 1)

from dbo.Numbers

where number <= len(@String_in))

select @String_out = coalesce(@String_out + S1_sub, S1_sub) -- puts the string back together

from s1

where @IncludeChars_in like '%' + S1_sub + '%' -- only include input list of allowed characters

order by number;

return @String_out;

end

Requires that you have a Numbers table, but you should have one of those for dozens of things anyway.

I ran a test on 10-thousand rows of data, and the one with the loop in it took an average of 13 seconds to run after the first run (to build the execution plan), while this version took an average of 4 seconds on the same data.

A single-pass, hard-coded replace command, on the other hand, only took 194 milliseconds to do the same thing:

update #T

set Col4 =

replace(

replace(col1, '1', ''),

'2', ''),

'3', ''),

'4', ''),

'5', ''),

'6', ''),

'7', ''),

'8', ''),

'9', ''),

'0', '')

So, if you really, really must have the flexibility to have a dynamic "accept these characters" string, then go with the Numbers table and the parsing function. If you can live without that, get 40 times the performance and hard-code the replace command.

Alternately, you could build the single-pass command dynamically and run it as dynamic SQL. That might or might not be better than either of the two inline functions (probably will be, but I haven't tested it).

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

GSquared (1/5/2009)
A single-pass, hard-coded replace command, on the other hand, only took 194 milliseconds to do the same thing:
update #T
set Col4 =
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(col1, '1', ''),
'2', ''),
'3', ''),
'4', ''),
'5', ''),
'6', ''),
'7', ''),
'8', ''),
'9', ''),
'0', '')
So, if you really, really must have the flexibility to have a dynamic "accept these characters" string, then go with the Numbers table and the parsing function. If you can live without that, get 40 times the performance and hard-code the replace command.

Strewth! Nice one GSquared, stored and noted for future reference.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden