Question

BAQ comma separated

  • 1 March 2021
  • 1 reply
  • 455 views
L
  • lpb
  • Inspiring
  • 26 replies

Hi,

I have a field in the database which has a string separated by commas.

is there away to separate them out in a BAQ to be put in a BAQ combo box.

 

cheers

1 reply

T
Userlevel 3

I’ve done this using an External BAQ in the past, the SQL is below. This doesn’t use any functions to avoid incompatibility between different versions of SQL.

SQL Server 2016 onwards has a String_Split function but I’m not sure if you can reference it from within a BAQ. It also requires the database to be at compatibility level 130 and above.

The example below uses the Part Table with Part.UserChar1 containing the comma separated value.

 SELECT Company, PartNum,  
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT Company, PartNum,  
         CAST ('<M>' + REPLACE([UserChar1], ',', '</M><M>') + '</M>' AS XML) AS String  
     FROM  Part) AS A CROSS APPLY String.nodes ('/M') AS Split(a)

 

Reply


Terms & Conditions