Powerquery SQL search for specific characters

Question

DR_code  DR_lastname  DR_markers  
ACE      Acerine      H,h,J,D,L,K   
MER      Merger       P,L,M,n,D  
SMI      Smith        D,K,M,b,v  
SMI1     Smith        L,K,M,H,f

Within the above SQL-table I would like to search for clients that match specific markers. These markers can be put in by the user eg D & K. The search has to be case sensitive.

sql="SELECT DR_LASTNAME WHERE DR_MARKER COLLATE Latin1_General_BIN LIKE '%D%' AND DR_MARKER COLLATE Latin1_General_BIN LIKE '%K%'"

The above works but I would like the user to be able to select a custom number of markers.

I have the selected markers in a variable in Powerquery eg VAR_MARKERS = "DK" but I can't get this to work:

VAR_MARKERS="DK",    
sql="SELECT DR_LASTNAME WHERE DR_MARKER CONTAINS".VAR_MARKERS.""

(1) Tag your question with the database you are using. (2) Fix your data model! Don't store multiple values in a single string column. That is not correct in any database. — Gordon Linoff
Or use JSON for the DR_markers column, and corresponding functions to search in it. — Michal J Figurski

astentx astentx · Accepted Answer · 2020-10-19T08:38:24

Does your database support regular expression? If so, then replace "&" with "|" (pipe) and use some function/predicate like "regexp_like". SQL Server example. Oracle example. Postgres example.

Powerquery SQL search for specific characters

1 Answers