Re: Integer to String Evaluation in SQL Statement

jhimschoot@CroweChizek.Com wrote: > > Hi all. Our environment is IDS V7.30.TC9X2 on NT. Does anyone know if > it's possible to convert/evaluate an integer value to a string in a SQL > statement? Yes it is. > My problem is that I have a column (col1) defined as an > integer, and I would like to find all rows that contain part of the number. > For example, I issue a query to locate all rows where col1 contains the > number 5678. The rows to be returned would include values 1356789, 443 > 5678, 567899, etc. Okay. > I know I can solve this issue by creating a temporary table containing all > distinct col1 values which would be inserted into a string column, and then > issue a SQL statement w/ the like clause. However, this is not the most > desirable way. No it is not. It's not as bad as a SP though. (Sorry Rudy ;-) > Any assistance, or ideas would be appreciated. Try something like: SELECT * FROM my_table WHERE col1 || "" LIKE "%5678%" Of course, this type of wildcard query could still be slow. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| | http://www.informix.com http://www.informixhandbook.com |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |What year 2000 bug? year 2000 bug? |/// / ////| | |year 2000 bug? year 2000 bug? year |// / /////| | |2000 bug? year 2000 bug? year 1900 |/ ////////| +----------------------+-----------------------------------+-----------+

Neat stuff !! Rudy "Mark D. Stock" wrote: > Try something like: > > SELECT * > FROM my_table > WHERE col1 || "" LIKE "%5678%"> > Of course, this type of wildcard query could still be slow. > > Cheers, > -- > Mark. > > +----------------------------------------------------------+-----------+ > | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| > | http://www.informix.com http://www.informixhandbook.com |///// / //| > | http://www.iiug.org +-----------------------------------+//// / ///| > | |What year 2000 bug? year 2000 bug? |/// / ////| > | |year 2000 bug? year 2000 bug? year |// / /////| > | |2000 bug? year 2000 bug? year 1900 |/ ////////| > +----------------------+-----------------------------------+-----------+

Mark D. Stock wrote: > jhimschoot@CroweChizek.Com wrote: > > > > Hi all. Our environment is IDS V7.30.TC9X2 on NT. Does anyone know if > > it's possible to convert/evaluate an integer value to a string in a SQL > > statement? > > Yes it is. > > > My problem is that I have a column (col1) defined as an > > integer, and I would like to find all rows that contain part of the number. > > For example, I issue a query to locate all rows where col1 contains the > > number 5678. The rows to be returned would include values 1356789, 443 > > 5678, 567899, etc. > > Okay. > > > I know I can solve this issue by creating a temporary table containing all > > distinct col1 values which would be inserted into a string column, and then > > issue a SQL statement w/ the like clause. However, this is not the most > > desirable way. > > No it is not. It's not as bad as a SP though. (Sorry Rudy ;-) > > > Any assistance, or ideas would be appreciated. > > Try something like: > > SELECT * > FROM my_table > WHERE col1 || "" LIKE "%5678%"> > Of course, this type of wildcard query could still be slow. or WHERE TO_CHAR(col1) LIKE "%5678%" if you have 7.30+ June -- june_t@hotmail.com Back from the dead, resuscitated by Godiva chocolates...

Not quite resuscitated - try some scotch as a chaser :-) ! TO_CHAR(), contrary to its "leading" name, only works on DATETIME variables. Rudy June Tong wrote: > > or > WHERE TO_CHAR(col1) LIKE "%5678%" > if you have 7.30+ > > June > -- > june_t@hotmail.com > Back from the dead, resuscitated by Godiva chocolates...

Why so it does (actually, DATE and DATETIME, according to the manual). Well, that sounds like a bug to me. The TO_CHAR() function was supposedly added for compatibility with Oracle, so that people wouldn't have to change existing Oracle code to run on Informix. And Oracle's TO_CHAR() works on integers; ergo, non-compatible. (Did you ever think you'd see the day when I knew Oracle, or even a function thereof, better than Informix? What is the world coming to?) June -- june_t@hotmail.com Back from the dead, resuscitated by Godiva chocolates... Rudy Fernandes wrote: > > Not quite resuscitated - try some scotch as a chaser :-) ! TO_CHAR(), > contrary to its "leading" name, only works on DATETIME variables. > > Rudy > > June Tong wrote: > > > > or > > WHERE TO_CHAR(col1) LIKE "%5678%" > > if you have 7.30+ > > > > June > > -- > > june_t@hotmail.com > > Back from the dead, resuscitated by Godiva chocolates...

Convert any column type to a string with: "" || colname June Tong <june_t@hotmail.com> wrote: >Why so it does (actually, DATE and DATETIME, according to the manual). >Well, that sounds like a bug to me. The TO_CHAR() function was >supposedly added for compatibility with Oracle, so that people wouldn't >have to change existing Oracle code to run on Informix. And Oracle's >TO_CHAR() works on integers; ergo, non-compatible. > >(Did you ever think you'd see the day when I knew Oracle, or even a >function thereof, better than Informix? What is the world coming to?) > >June >-- >june_t@hotmail.com >Back from the dead, resuscitated by Godiva chocolates... > > >Rudy Fernandes wrote: >> >> Not quite resuscitated - try some scotch as a chaser :-) ! TO_CHAR(), >> contrary to its "leading" name, only works on DATETIME variables. >> >> Rudy >> >> June Tong wrote: >> > >> > or >> > WHERE TO_CHAR(col1) LIKE "%5678%" >> > if you have 7.30+ >> > >> > June >> > -- >> > june_t@hotmail.com >> > Back from the dead, resuscitated by Godiva chocolates...