October 17, 2007 at 9:05 am
hello,
i have a question:
i have a string and i need to remove the numbers from it. something like oracle's TRANSELATE.
how i do it in T-SQL?
thank you.
October 17, 2007 at 2:32 pm
This ought to work. Jeff Moden helped me with a couple of these, and I think this is one of them, but I can not remember exactly, but it works. Just a UDF to omit numbers from a string. It will not touch special characters.
CREATE FUNCTION fnDeleteNumbers (@String VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Return VARCHAR(8000)
SET @Return = ''
SELECT @Return = @Return+SUBSTRING(@String,N,1)
FROM Tally
WHERE N <= LEN(@String)
AND SUBSTRING(@String,N,1) LIKE '%[^0-9]%'
RETURN @Return
END
LIKE '%[^0-9]% --this is the meat of it. If you only want to see numbers you can remove the '^'. Or you can say [^a-z] for removing letters, or anything you want.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 17, 2007 at 3:37 pm
Greg Snidow (10/17/2007)
This ought to work. Jeff Moden helped me with a couple of these, and I think this is one of them, but I can not remember exactly, but it works. Just a UDF to omit numbers from a string. It will not touch special characters.CREATE FUNCTION fnDeleteNumbers (@String VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Return VARCHAR(8000)
SET @Return = ''
SELECT @Return = @Return+SUBSTRING(@String,N,1)
FROM Tally
WHERE N <= LEN(@String)
AND SUBSTRING(@String,N,1) LIKE '%[^0-9]%'
RETURN @Return
END
LIKE '%[^0-9]% --this is the meat of it. If you only want to see numbers you can remove the '^'. Or you can say [^a-z] for removing letters, or anything you want.
thanks. i'll try it. but i dont really understand the code:
"Tally": what doest it mean? i'm selecting a string, so why FROM is required?
"N": can i just write it like that? without declaring it anywhere?
October 17, 2007 at 4:02 pm
Tally is a table that you need to create with the column N. The fill it with the numbers 1 to whatever you think you need. This is from the Jeff Moden toolbox and is useful in many ways.
--copied shamelessly from a Jeff Moden Example
--Now, before we get to the solution, we need to make a well indexed table of sequential numbers. These "Tally" or "Numbers" tables are very powerful and can help do things in SQL Server 2000 as if we were using ROWNUM from SQL Server 2005. You should make a permanent Tally table as follows... yes, this is part of the solution for this and many other "impossible" tasks...
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
October 17, 2007 at 5:43 pm
vadimt (10/17/2007)
Greg Snidow (10/17/2007)
"Tally": what doest it mean? i'm selecting a string, so why FROM is required?
"N": can i just write it like that? without declaring it anywhere?
I am so sorry about that, I just pasted it without really thinking about what was in it as I was closing down for the day. Mrpolecats code will create one for you, but I can not really explain how it works. I can not really do it justice, but here is a simple example
DECLARE @String VARCHAR(100)
SELECT @String = 'abcdefghijklmnopqrstuvwxyz'
SELECT SUBSTRING(@String,n,1)
FROM tally
WHERE n<= LEN(@String)
The tally table is simply a table of numbers from 1 to whatever, mine goes up to 100,000, but I have never needed that much. In the above code, and in Jeff's function I posted earlier, by joining to the tally table you are able to step through the string one character at a time. For n = 1, the first number in the tally table, think of the SELECT like this: SELECT(@String,1,1). For n=2 it will be SELECT(@String,2,1). The SELECT statement is limited by restricting the n's you will use to the length of your string. In the function example, the code, by joining to the tally table, is asking itself,is the first character(n=1) a number?. Is n=2 a number? It will look at each character one at a time, and only return the ones that fit the criteria '%[^0-9]%'. I asked Jeff to explain it to me a couple of months ago, and I did not really get it. Once you start playing with it you will see what a good tool it is. Let me tell you that I am not a DBA, nor a programmer, I just know enough to keep my head dry, so I hope one of the regulars here can explain it to you better.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 17, 2007 at 7:08 pm
perhaps function like this may help you.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udfTRANSELATE] (@mixedString AS varchar(max))
RETURNS varchar(max)
as
BEGIN
DECLARE @resultString varchar(max), @tmpString char(1), @i int, @strLen int
SELECT @resultString='', @i=0, @strLen=len(@mixedString)
WHILE @strLen>=@i
BEGIN
SELECT @tmpString=substring (@mixedString,@i/*character index*/,1/*one character*/)
if isnumeric(@tmpString)=0
SELECT @resultString=@resultString+@tmpString
SELECT @i=@i+1
END
RETURN @resultString
END
ISNUMERIC is the heart of the function.
According to the BOL:
ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type
So, .+- and currency signs are convertable and therefore they will be removed as well. However it would not be difficult to check for these in the input string and simply add it to the finall string.
October 17, 2007 at 7:19 pm
Max Yasnytskyy (10/17/2007)
ISNUMERIC is the heart of the function.According to the BOL:
ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type
So, .+- and currency signs are convertable and therefore they will be removed as well. However it would not be difficult to check for these in the input string and simply add it to the finall string.
Bad heart for the function.
'3E5' is valid floating point number.
As well as 'PI()' and many other non-number strings.
Not sure it's what OP needs.
_____________
Code for TallyGenerator
October 17, 2007 at 7:25 pm
Well spotted
But because i take a single character at a time '3e5' will never be validated as a whole...
October 17, 2007 at 8:00 pm
OK, what about '-', '.', ',', who knows what else?
_____________
Code for TallyGenerator
October 17, 2007 at 8:15 pm
Sergiy, if you were reading my post a bit more carefully you would have noticed that it is only integer, floating point number, money or decimal types are returning 1. Hence .+- and all currency signs will return 1, as far as i can understand this list is exhaustive
October 17, 2007 at 8:26 pm
I have found an interesintg function in msdb, seems like it is working much better
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udfTRANSELATE] (@mixedString AS varchar(max))
RETURNS varchar(max)
as
BEGIN
DECLARE @resultString varchar(max), @tmpString char(1), @i int, @strLen int
SELECT @resultString='', @i=0, @strLen=len(@mixedString)
WHILE @strLen>=@i
BEGIN
SELECT @tmpString=substring (@mixedString,@i/*character index*/,1/*one character*/)
if msdb.dbo.ConvertToInt (@tmpString,11,11)=11
SELECT @resultString=@resultString+@tmpString
SELECT @i=@i+1
END
RETURN @resultString
END
October 17, 2007 at 8:27 pm
Max Yasnytskyy (10/17/2007)
Sergiy, if you were reading my post a bit more carefully you would have noticed that it is only integer, floating point number, money or decimal types are returning 1. Hence .+- and all currency signs will return 1, as far as i can understand this list is exhaustive
Yes, I read you post.
But as my example with dot and comma shows this list IN NOT EXHAUSTIVE.
And funny side is - list of allowed characters is way shorter then the list of exclusions from ISNUMERIC.
Why not just use LIKE [0-9]?
_____________
Code for TallyGenerator
October 17, 2007 at 8:47 pm
Sergiy (10/17/2007)
Max Yasnytskyy (10/17/2007)
Sergiy, if you were reading my post a bit more carefully you would have noticed that it is only integer, floating point number, money or decimal types are returning 1. Hence .+- and all currency signs will return 1, as far as i can understand this list is exhaustiveYes, I read you post.
But as my example with dot and comma shows this list IN NOT EXHAUSTIVE.
And funny side is - list of allowed characters is way shorter then the list of exclusions from ISNUMERIC.
Why not just use LIKE [0-9]?
Fair enough.
How would you use LIKE [0-9] without making the query too complicated?
October 17, 2007 at 8:52 pm
Max Yasnytskyy (10/17/2007)
Fair enough.How would you use LIKE [0-9] without making the query too complicated?
In your script:
IF @TmpString NOT LIKE [0-9]
Not too complicated?
:hehe:
_____________
Code for TallyGenerator
October 17, 2007 at 9:01 pm
Sergiy (10/17/2007)
Max Yasnytskyy (10/17/2007)
Fair enough.How would you use LIKE [0-9] without making the query too complicated?
In your script:
IF @TmpString NOT LIKE [0-9]
Not too complicated?
:hehe:
Can i have a working example please
When i replace if msdb.dbo.ConvertToInt (@tmpString,11,11)=11
with IF @TmpString NOT LIKE [0-9]
i get
Msg 207, Level 16, State 1, Procedure udfTRANSELATE, Line 11
Invalid column name '0-9'.
Viewing 15 posts - 1 through 15 (of 172 total)
You must be logged in to reply to this topic. Login to reply