Results 1 to 3 of 3

Thread: seeking help in LIKE / MATCHES in IBM informix sql

  1. #1
    Join Date
    Aug 2021
    Posts
    2

    seeking help in LIKE / MATCHES in IBM informix sql

    Hi all

    I am new to IBM Informix ,

    select * from TEST_TABLE where c2 like '5 %';

    below are the results returned,
    c1 ABC
    c2 5

    c1 FGH
    c2 5 ½

    I am concerned about the row c2 5 why is it returning 5 when I am looking for 5<space>%

    Note: c2 is a varchar and it did not have any trailing spaces.

    Can anyone let me know the reason of this wierdness...

    Thank you so much

  2. #2
    Join Date
    Aug 2021
    Posts
    2
    correction

    Hi all

    I am new to IBM Informix ,

    select * from TEST_TABLE where c2 like '5 %';

    below are the results returned,

    c2 5


    c2 5 ½

    I am concerned about the row c2 5 why is it returning 5 when I am looking for 5<space>%

    Note: c2 is a varchar and it did not have any trailing spaces.

    Can anyone let me know the reason of this wierdness...

    Thank you so much

  3. #3
    Join Date
    Aug 2022
    Posts
    22
    The reason that the query is returning a row where c2 = '5' is that the '%' wildcard character in a SQL LIKE statement represents zero or more characters, not just spaces. In your query, the statement is looking for any value in the c2 column that starts with '5' followed by zero or more characters. Therefore, the value '5' is being returned because it starts with '5' even though it doesn't have a space after it.

    To return only the rows where c2 has a space after the '5', you could use the '_' wildcard character, which represents a single character. Your query would look like this:

    select * from TEST_TABLE where c2 like '5 _%';

    This will match only the rows where c2 starts with '5' followed by a space, and then zero or more characters.

    Alternatively, you can use the TRIM() function to remove trailing spaces from the c2 column before you run the query. For example:

    select * from TEST_TABLE where TRIM(TRAILING FROM c2) like '5 %';

    This will first remove any trailing spaces from c2 and then run the query as before.

    Please note that IBM Informix SQL uses different syntax for trimming the trailing spaces.

    select * from TEST_TABLE where RTRIM(c2) like '5 %';

    This will remove trailing spaces from the c2 column and then run the query as before.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •