Forum Discussion

Deleted's avatar
Deleted
Aug 03, 2020

Alternatives to using Concat for SQL queries

I am trying to find alternative ways to using Excel's concate function for a SQL Query and any help would be greatly appreciated. Specifically if anyone knows of a way to rewrite this using Textjoin ...
excel
Formulas and Functions
IngeborgHawighorst's avatar
IngeborgHawighorst
MVP
Aug 03, 2020

Hello Deleted 

 

TextJoin() is used to combine contents from different cells. You can specify a delimiter and you can ignore empty cells.

 

If you want to use TextJoin, then all the elements need to be in cells. Your current formula has only a few elements of the resulting text string in cells, and other elements are hard coded. This cannot be used with TextJoin. So, you would need to enter the text constants into cells, too.

 

The formula in cell E5 is  =TEXTJOIN(,TRUE,A5,A3,A6,B3,A7,B3,A8,A3,A9,B3,A10)

 

But you don't really need Concat() either. You can use the ampersand character to append different text strings. The formula in cell E4 produces the same result with

 

="select count(1) as rec_cnt,'"&A3&"' as tbl_nm, '"&B3&"' as col_nm, case when length(nvl("&B3&",0)) > 7 then 'GT 7' else 'LTE 7' end as col_len from dwh02."&A3&" group by case when length(nvl("&B3&",0)) > 7 then 'GT 7' else 'LTE 7' end union"

 

Resources