|
Imagine a scenario where you have a datafield containing a series of identifiers with diferent prefixes that are alpha characters but are different lengths and you need to remove them to leave only the numbers. I came across the situation when trying to link up two systems, one system contained numeric identifers, and the other had the same numeric identifier but prefixed with a 2 or 3 letter code to denote the company it was attached to.
This was a quick function I put together to allow me to build a view with data from both systems.
create function NoLetters(@number varchar(20)) returns varchar(20)
as
begin
declare @c int
set @c=65
while @c<65+26
begin
set @number=replace(@number,char(@c),'')
set @c=@c+1
end
return(@number)
end
To use the function it can be added to a print statement like
PRINT dbo.NoLetters('ABC1234')
This will return 1234
A more useful implimentation is something like
Select * from table1 t1 join table2 t2 on t1.idfield=dbo.noletters(t2.idfield)
This would allow the joining of two systems even though the formats of the identifers are slightly different.
When calling the function it usually must be prefixed with the owner of the function, in the above example I used dbo ecuase my account has dbo owner rights on the database, but this mght change depending on your own set of access rights.
|
21 November 2008 IB
SQL Server
|
Back To List
Did this article answer your question |
 |
Yes |
 |
No |
|
Other items of interest in our knowledgebase
|
