Stripping alpha numeric charecters

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)

Stripping alpha numeric charecters

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2009-01-02 : 21:38:58

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-01-02 : 22:23:17

Change the update statement to:
update test14 set fname_bak = dbo.GetCharacters(fname,'a-z')

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2009-01-02 : 22:55:00

quote:
Originally posted by sunitabeck

Change the update statement to:
update test14 set fname_bak = dbo.GetCharacters(fname,'a-z')

this statement will error out since you cannot pass function in the update statement.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-03 : 07:28:20

[code]
update test14
set fname_bak = r.result
from test14
join
(select tt.id,dbo.GetCharacters (t.fname, 'a-z') as result
from test14 tt
inner join t
on t.id = tt.id
)r
on r.id=test14.id
[/code]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-01-03 : 07:44:50

quote:
Originally posted by scelamko

quote:
Originally posted by sunitabeck

Change the update statement to:
update test14 set fname_bak = dbo.GetCharacters(fname,'a-z')

this statement will error out since you cannot pass function in the update statement.

I don't have access to a SQL server right now, so I can't verify that it will/will not work, so I will take your word for it. But, I could have sworn there is no such restriction.

The only thing that would concern me is that the update would not be very efficient because the function will not be in-line.

What was the error message it gave?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-03 : 07:58:08

quote:
Originally posted by sunitabeck

quote:
Originally posted by scelamko

quote:
Originally posted by sunitabeck

Change the update statement to:
update test14 set fname_bak = dbo.GetCharacters(fname,'a-z')

this statement will error out since you cannot pass function in the update statement.

I don't have access to a SQL server right now, so I can't verify that it will/will not work, so I will take your word for it. But, I could have sworn there is no such restriction.

The only thing that would concern me is that the update would not be very efficient because the function will not be in-line.

What was the error message it gave?

didnt understand what you mean by function will not be inline. can you please elaborate?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-01-03 : 08:32:32

quote:
Originally posted by visakh16
didnt understand what you mean by function will not be inline. can you please elaborate?

What I mean is that there will be the overhead of one function call per each row updated (assuming, of course, that it will work to begin with, and won't give an error as scelamko had indicated).

If we were to use an an in-line expression instead of the udf, there would of course, be no function call overhead. If the udf did not need the fname input from the outer table, I can see how the query optimizer can avoid the one function call per row, and would be just as efficient as an inline expression. But the presence of the fname input from the outer table would force it to make one function call per row updated.

(I don't have a SQL server, so I am guessing all this - I have not looked at the query plan or tried it, so if this conjecture is not correct, I stand corrected).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-03 : 08:38:24

quote:
Originally posted by sunitabeck

quote:
Originally posted by visakh16
didnt understand what you mean by function will not be inline. can you please elaborate?

What I mean is that there will be the overhead of one function call per each row updated (assuming, of course, that it will work to begin with, and won't give an error as scelamko had indicated).

If we were to use an an in-line expression instead of the udf, there would of course, be no function call overhead. If the udf did not need the fname input from the outer table, I can see how the query optimizer can avoid the one function call per row, and would be just as efficient as an inline expression. But the presence of the fname input from the outer table would force it to make one function call per row updated.

(I don't have a SQL server, so I am guessing all this - I have not looked at the query plan or tried it, so if this conjecture is not correct, I stand corrected).

ok that explanation makes sense.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources