CODEWITHSUNDEEP
sqlhana
shoyeb shahriar
shoyeb shahriar

Reputation: 11

How to filter String in where clause

I would like to extract the string using where clause in SAP HANA.For an example,these are 3 strings for name column.

123._SYS_BIC.meag.app.qthor.cidwh_eingangsschicht.backend.dblayer.l2.checks/MasterData_Holdings.
153._SYS_BIC.meag.app.qthor.centralAdministration.backend.dblayer.l2.checks/AuditAndSecurities.
meag.app.qthor.centralAdministration.backend.dblayer.l2.checks/GeneralLedger

After filter the name column using where clause, output in the name column would be shown only the last portion of the string. So, output will be like this. That means whatever we have, just remove from the beginning till '/'.

"MasterData_Holdings" "AuditAndSecurities" "GeneralLedger"

Upvotes: 1

Views: 866

Answers (1)

Jorge Campos
Jorge Campos

Reputation: 23371

You can try using the REPLACE_REGEXPR

I'm not familiar myself with Hana but the function is pretty straight forward and it should be:

select REPLACE_REGEXPR('.+/(.+)' IN fieldName WITH '\1' OCCURRENCE ALL) as field
...
where
... -- your filter

Be aware that this regex '.+/(.+)' will eat everything until the last / so for instance if you have ....checks/MasterData_Holdings/Something it will return only Something

Upvotes: 1

Related Questions