How to retrieve only integer value from a column having datatype as varchar.

UDA
K_Y
N/A

How to retrieve only integer value from a column having datatype as varchar.

Hi

 

I am having varchar type column which has string as well as integer but no alphanumeric value.  How can I just fetch the integer value ignoring the string value considering that there is no common string value, the text can be different, like one value can be 'true' and other can be 'false' and one 2134, i only want the 2134, can't give filter on true and false because there can be other strings too which i am not aware of.

 

Please help, any idea will be appricaited.

 

Thanks

Kavita


Accepted Solutions
N/A

Re: How to retrieve only integer value from a column having datatype as varchar.

beside to_number you might check for the regex functions.

as always it helps if you provide some example code as code...

 

create table a (a varchar(10032)) no primary index;

delete from a;
insert into a ('ask212k1');
insert into a ('212k1');
insert into a ('212');
insert into a ('21293923');
insert into a ('2129392d32')
insert into a ('2129392.32')

select cast(a as bigint)
from a
where regexp_instr(a,'^[0-9]+$') = 1
;

select cast(a as bigint)
from a
where to_number(a) is not null;

drop table a;
1 ACCEPTED SOLUTION
10 REPLIES

Re: How to retrieve only integer value from a column having datatype as varchar.

Hi Kavita,

 

Have you tried using the TO_NUMBER function in a WHERE clause? Something like:

SELECT col1
  ,col2
  ,...
FROM table
WHERE TO_NUMBER(col1) IS NOT NULL
...

The TO_NUMBER function will return NULL if the data fails to convert to a number.

 

Look in chapter 14 in the SQL Functions manual.

 

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
N/A

Re: How to retrieve only integer value from a column having datatype as varchar.

beside to_number you might check for the regex functions.

as always it helps if you provide some example code as code...

 

create table a (a varchar(10032)) no primary index;

delete from a;
insert into a ('ask212k1');
insert into a ('212k1');
insert into a ('212');
insert into a ('21293923');
insert into a ('2129392d32')
insert into a ('2129392.32')

select cast(a as bigint)
from a
where regexp_instr(a,'^[0-9]+$') = 1
;

select cast(a as bigint)
from a
where to_number(a) is not null;

drop table a;

Re: How to retrieve only integer value from a column having datatype as varchar.

Hi Kavita,

 

Dave is completely correct.

Here is some test data, since I already made the query.

-- Creating sample data
CREATE MULTISET VOLATILE TABLE TEST
(
   TEST_VALUES VARCHAR(100)
)
ON COMMIT PRESERVE ROWS;

INSERT INTO TEST VALUES ('true');
INSERT INTO TEST VALUES ('false');
INSERT INTO TEST VALUES ('2134');
INSERT INTO TEST VALUES ('true1');
INSERT INTO TEST VALUES ('false1');
INSERT INTO TEST VALUES ('21342134');

-- Your required query
SELECT *
FROM
TEST
WHERE
TO_NUMBER(TEST_VALUES) IS NOT NULL;

Thanks,

Rohan Sawant

ROHAÑ
K_Y
N/A

Re: How to retrieve only integer value from a column having datatype as varchar.

Hi

 

Thank you so much all for your suggesstion, I tried using to_number function but it says " Number string has more than 128 characters" is there a way to reslove it using to_number function itself.

Highlighted

Re: How to retrieve only integer value from a column having datatype as varchar.

Hi,

 

Can you give an example of your code that generates this error?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
K_Y
N/A

Re: How to retrieve only integer value from a column having datatype as varchar.

Hi Dave

 

If I test to_number function with some sample value it works fine for me, but my table has very large dataset, and i am not sure what is the max length of the data present in the column the the data type is specified as varchar(4000), so with the actual data set of the table to_number function is throwing the error. A sample code similer to my code, where Value1 column has datatype as varchar(4000).

 

Select

cast(VALUE1 as decimal(38,0)) as VALUE1
from
Value_table

WHERE
cal_dt BETWEEN '2017-08-02' and '2017-08-08'
AND
VALUE1 IS NOT NULL
AND TO_NUMBER(VALUE1) IS NOT NULL

 

Thanks

Kavita

Re: How to retrieve only integer value from a column having datatype as varchar.

Hi Kavita,

 

Can you let us know what is the error?

 

Thanks,

Rohan Sawant

ROHAÑ

Re: How to retrieve only integer value from a column having datatype as varchar.

I tested this out on my 15.10 system and generated a " SELECT Failed.  [9134] Number string has more than 128 characters. "

error.

 

I'm confused because according to the DBC.ColumnsV definitions for the parameters to this function the input parameter is VARCHAR(32000).

 

My test code that generated the error was:

SELECT *
FROM (SELECT a.*
                  ,a.commentstring||'~'||a.commentstring||'~'||a.commentstring||'~'||a.commentstring||'~'||a.commentstring||'~'||a.commentstring||'~'||a.commentstring||'~'||a.commentstring||'~'||a.commentstring||'~'||a.commentstring AS b
              FROM dbc.databasesv AS a
			  WHERE commentstring IS NOT NULL) AS dt1
WHERE TO_NUMBER(b,'zzzzzzzzzzzzzzzzzz9') IS NOT NULL

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
N/A

Re: How to retrieve only integer value from a column having datatype as varchar.

Simply check the length of the input before TO_NUMBER:

 WHERE To_Number (CASE WHEN Char_Length(x) < 128 THEN x END) IS NOT NULL

In TD15.10+ you can also use TRYCAST(x AS INT) instead.