9D (Hex) character remove in sqlite

问题:

i want select family from my_table where family LIKE '%HEX(9D)' family hex format ended with 9D hex i convert excel file in to sqlite database but some of my data add 9D in ended i can not search correctly please guide me how can remove 9D hex character in family column?

回答1:

To get HEX(9D) in SQLLite use cast(X'9D' as text). So to update use something like:

UPDATE YOURTABLE SET YOURCOLUMN=REPLACE(YOURCOLUMN,cast(X'9D' as text),'') where YOURCOLUMN like '%'||cast(X'9D' as text)||'%';

or just

UPDATE YOURTABLE SET YOURCOLUMN=REPLACE(YOURCOLUMN,X'9D','') where YOURCOLUMN like '%'||x'9D'||'%';