Convert a string to a integer

[Almie]

I am using a Informix database.
I have a concatenated case number,data type of a string (S-0100-CR-20010101) that I am trying to parse into four different fields that will link to another table where the case number already separated into four fields (S string(4),0100 integer, CR string(6),20010101 integer).

How do you write the sql so that the 2nd and 4th field data have an integer data type.

This is my query:
SELECT
SUBSTR(case_num,1,1) cs_num_1,
SUBSTR(case_num,3,4) cs_num_2,
SUBSTR(case_num,8,2) cs_num_3,
SUBSTR(case_num,11,11) cs_num_4,
prty_type,
prty_num
FROM fr_qual


Thank you in advance for your help! =o)

 

[PHV]

The ANSI SQL syntax:
SELECT
SUBSTRING(case_num FROM 1 FOR 1) cs_num_1,
CAST(SUBSTRING(case_num FROM 3 FOR 4) AS INTEGER) cs_num_2,
SUBSTRING(case_num FROM 8 FOR 2) cs_num_3,
CAST(SUBSTRING(case_num FROM 11 FOR 11) AS INTEGER) cs_num_4,
prty_type,
prty_num
FROM fr_qual

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Almie]

I am receiving a syntax error message/-201 error message. I've checked and rechecked my query and I can't find the problem. Can anyone see what I can't?

SELECT
SUBSTR(case_num ,1,1) cs_num_1,
CAST(SUBSTR(case_num,3,4) AS INT) cs_num_2,
SUBSTR(case_num,8,2) cs_num_3,
CAST(SUBSTR(case_num,11,11) AS INT) cs_num_4,
prty_type,
prty_num
FROM fr_qual

When I use this query, I get General error/Syntax error/-11060 error message:

SELECT
SUBSTR(case_num from 1 for 1) cs_num_1,
CAST(SUBSTR(case_num from 3 for 4) AS INT) cs_num_2,
SUBSTR(case_num from 8 for 2) cs_num_3,
CAST(SUBSTR(case_num from 11 for 11) AS INT) cs_num_4,
prty_type,
prty_num
FROM fr_qual


Can anyone see what I can't?

 

[notadba]

I think the error you are getting is caused by the
CAST(SUBSTR(case_num,11,11) AS INT) cs_num_4

The field you have provided is only 18 chars - (S-0100-CR-20010101), so the sql is looking for the 11th posion and then the next 11... which it can't do. Try 11,8?