I want to compare a string which has trailing spaces at the end. I do not want to use the LIKE operator. Is there any other way to consider the trailing space during string comparison?

Help
1

Challenge - we have two records which have a String column that has values that differ by trailing space and the comparison is bringing back both records instead of one with the trailing space that we need.

2

You could try the TRIM function:

like

select * from t where trim(trailing from t.a) = trim(trailing from t.b);

3

Trim will remove the spaces and compare and i cannot update the right record based on that comparison. i want to compare the spaces too.
Say, I have two records with following -
sys id, Name
1000, “ABC”
1001,"ABC "

If i want to update the record (with sys id 1001) by comparing the name "ABC ", then how can i do that?

4

@sdevaiah I believe you can also add a condition to compare the actual length of the data.
Say

t.a = 'text ’ and length(t.a) = length('text ')

1 Like
5

Consider also using a binary collation, e.g.:

singlestore> select 'text' collate utf8_bin = 'text ' collate utf8_bin :> blob;
+------------------------------------------------------------+
| 'text' collate utf8_bin = 'text ' collate utf8_bin :> blob |
+------------------------------------------------------------+
|                                                          0 |
+------------------------------------------------------------+
1 row in set (0.01 sec)

singlestore> select 'text' collate utf8_bin = 'text' collate utf8_bin :> blob;
+-----------------------------------------------------------+
| 'text' collate utf8_bin = 'text' collate utf8_bin :> blob |
+-----------------------------------------------------------+
|                                                         1 |
+-----------------------------------------------------------+