Select Only Numbers From Varchar column

Question

In SQL Server 2008 R2 I have some data in a varchar(12) column, it looks something like this:

Data:
%%1234
%1765
34566
123
%SDRMH
HJG434

I'd like to drop the '%' from all the rows that have it and also only select those which are numbers. I've already tried to use the ISNUMERIC() function, but it looks like that leaves the % in the rows.

Any help is greatly appreciated.

score 2 · Accepted Answer · answered Jan 02 '14 at 16:39

2

You can use a combination of REPLACE and ISNUMERIC to achieve the result set you want:

SELECT REPLACE(columnName,'%','') FROM tableName
WHERE ISNUMERIC(REPLACE(columnName,'%','')) = 1

answered Jan 02 '14 at 16:39

steoleary

8,968
2
33
47

score 1 · Answer 2 · answered Jan 02 '14 at 16:35

1

You could use the REPLACE function to strip out all the % instances

SELECT REPLACE(column_name,'%','');
GO

answered Jan 02 '14 at 16:35

Mike

2,391
6
33
72

he would still get values like 'SDRMH' and that's not what he wants – Vland Jan 02 '14 at 18:01

score 1 · Answer 3 · answered Jan 02 '14 at 16:42

This function would be expensive to use, but it could help you create / populate a new column properly typed as an int (for example):

create function [dbo].[is_int]
    (   @value as varchar(max)  )
returns int
as
begin
declare @return int
set @return = 5
while len(@value) > 0
begin
    if left(@value,1) in('1','2','3','4','5','6','7','8','9','0')
        begin
            set @value = right(@value, len(@value)-1)
            set @return = 1
        end
    else
        begin
            set @return = 0
            set @value = ''
        end
end
return @return
end

or you could modify it to return the integers themselves, instead of a true/false (1/0).

score 0 · Answer 4 · answered Jan 02 '14 at 16:39

0

You could do something like

SELECT REPLACE(column,'%','') WHERE ISNUMERIC(REPLACE(column,'%','')) = 1

answered Jan 02 '14 at 16:39

user2989408

3,127
1
17
15

Select Only Numbers From Varchar column

4 Answers4

Linked