Need impex export script to export all the product from staged with description containing question mark symbol

[Former Member]

Hi Experts,

I need impex export script to export all the product from staged with product code and product description containing question mark sysmbol(?). I tried to in many ways using LIKE operator such as WHERE {p.description[en]} LIKE '%?%' or {p.description[en]} LIKE '%[?]%', but didn't find any result. Please find full query and help me out on resolving this.

INSERT_UPDATE Product;code[unique=true];description[lang = en]; "#%impex.exportItems( ""select {p.pk},{p.code},{p.description} from {Product as p},{Catalog as c},{CatalogVersion as cv} WHERE {p.description[en]} LIKE '%?%' AND {cv.catalog}={c.pk} AND {cv.version}='Staged' AND {cv.pk}={p.catalogVersion}}"",Collections.EMPTY_MAP, Collections.singletonList( Item.class ), true, true, -1, -1 );"

Thanks

[andyfletcher · Accepted solution]

It depends on which database you are using but if you're using MySQL then try using concat and char functions to stop Hybris trying to parse the ? as a parameter.

e.g.

 select {pk} from {product} where {description} like concat('%',char(63),'%')

(63 is the character code for ?)

You'll be able to find similar functions for other db types (I believe this would work as is for SQL Server and HANA too, but Oracle uses a chr function instead)

[Former Member · Accepted solution]

Thank you for the response. The above query is working fine in MySQL and HSQL but we are using Oracle DB, so for oracle the below query worked. select {pk} from {product} where {description} like '%'||CHR(63)||'%'

[Former Member]

Thank you for the response. The above query is working fine in MySQL and HSQL but we are using Oracle DB, so for oracle the below query worked. select {pk} from {product} where {description} like '%'||CHR(63)||'%'