February 3, 2003 at 9:09 am
I have a column that contains both character and numerical data. What funtions could I use to select only the numeric data in the column.
Example: Column data may be 'a.j. store # 355'
I only want to return the 355.
Any help whould be greatly appreciated.
February 3, 2003 at 9:49 am
You could create a function like so:
CREATE FUNCTION only_num
(@input char(100))
RETURNS char(100)
AS
begin
declare @output char(100)
set @output = ''
while len(@input) > 0
begin
if substring(@input,1,1) >= '0' and substring(@input,1,1) <= '9'
set @output = rtrim(@output) + substring(@input,1,1)
set @input = substring(@input,2,len(@input))
end
RETURN (@output)
end
GO
declare @x varchar(100)
set @x = 'a.j. store # 355'
select dbo.only_num(@x)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
February 3, 2003 at 9:55 am
Thanks Greg, that works nicely.
February 3, 2003 at 11:05 am
Of course, it is preferable in SQL to use a set-based operations instead of loops. Pattern matching capabilities will help to reslve your puzzle. Try something like this (details will depend on the requirements:
select RIGHT( column_name, LEN( column_name ) - PATINDEX( '%[0-9]%', column_name ) + 1 )
Also, look in Help for all patterns that SQL supports. For example, ^ means 'not'.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply