seeking help in LIKE / MATCHES in IBM informix sql

[stanats]

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

[stanats]

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

[johnsonmillie53]

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.