postgresql 查询sql字符串拼接相关

梦里梦外; 2022-11-20 08:03 1423阅读 0赞

###                               postgresql  字符串的两种连接方式 ###

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#000000 #dddddd #000000 #000000;text-align:center;vertical-align:middle;width:251.25pt;">函数</td> 
 <td style="border-color:#000000 #dddddd;text-align:center;vertical-align:middle;width:43.5pt;">返回类型</td> 
 <td style="border-color:#000000 #dddddd;text-align:center;vertical-align:middle;width:141.75pt;">描述</td> 
 <td style="border-color:#000000 #dddddd;text-align:center;vertical-align:middle;width:168.75pt;">样例</td> 
 <td style="border-color:#000000 #000000 #000000 #dddddd;text-align:center;vertical-align:middle;width:70.5pt;">结果</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000 #dddddd #000000 #000000;vertical-align:middle;width:251.25pt;">string&nbsp;||&nbsp;string</td> 
 <td style="border-color:#000000 #dddddd;vertical-align:middle;width:43.5pt;">字符串</td> 
 <td style="border-color:#000000 #dddddd;vertical-align:middle;width:141.75pt;">字符串拼接</td> 
 <td style="border-color:#000000 #dddddd;vertical-align:middle;width:168.75pt;">P' || 'SQL'</td> 
 <td style="border-color:#000000 #000000 #000000 #dddddd;vertical-align:middle;width:70.5pt;">PSQL</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000 #dddddd #000000 #000000;vertical-align:middle;width:251.25pt;">string&nbsp;||&nbsp;non-string&nbsp;or&nbsp;non-string&nbsp;||&nbsp;string</td> 
 <td style="border-color:#000000 #dddddd;vertical-align:middle;width:43.5pt;">text</td> 
 <td style="border-color:#000000 #dddddd;vertical-align:middle;width:141.75pt;">拼接空字符串</td> 
 <td style="border-color:#000000 #dddddd;vertical-align:middle;width:168.75pt;">‘Value: ' || 1</td> 
 <td style="border-color:#000000 #000000 #000000 #dddddd;vertical-align:middle;width:70.5pt;">Value: 1</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000 #dddddd #000000 #000000;vertical-align:middle;width:251.25pt;">concat(str&nbsp;"any"&nbsp;[,&nbsp;str&nbsp;"any"&nbsp;[, ...] ])</td> 
 <td style="border-color:#000000 #dddddd;vertical-align:middle;width:43.5pt;">字符串</td> 
 <td style="border-color:#000000 #dddddd;vertical-align:middle;width:141.75pt;">拼接函数里所有的字符串参数，忽略空字符串</td> 
 <td style="border-color:#000000 #dddddd;vertical-align:middle;width:168.75pt;">concat('abc', age, NULL, 9)</td> 
 <td style="border-color:#000000 #000000 #000000 #dddddd;vertical-align:middle;width:70.5pt;">abcage9</td> 
 </tr> 
 <tr> 
 <td style="border-color:#000000 #dddddd #000000 #000000;vertical-align:middle;width:251.25pt;">concat_ws(sep&nbsp;text,&nbsp;str&nbsp;"any"&nbsp;[,&nbsp;str&nbsp;"any"&nbsp;[, ...] ])</td> 
 <td style="border-color:#000000 #dddddd;vertical-align:middle;width:43.5pt;">字符串</td> 
 <td style="border-color:#000000 #dddddd;vertical-align:middle;width:141.75pt;">第一个参数作为分隔符，拼接其他的几个参数</td> 
 <td style="border-color:#000000 #dddddd;vertical-align:middle;width:168.75pt;">concat_ws(',', 'abc', age, NULL, 9)</td> 
 <td style="border-color:#000000 #000000 #000000 #dddddd;vertical-align:middle;width:70.5pt;">abc,age,9</td> 
 </tr> 
 </tbody> 
</table>

分别举例：  
1、sql字符串拼接

select '\{"adminCodes":\['||code||'\],"name":"'||name||'"\}' from admin\_mapping limit 1000 ;

select '"po\#'||name||'\#latest":"dfdxcxcf"' from admin\_mapping limit 1000 ;

select string\_agg(name||'' , ',') from user

2、拼接空字符串

select  '!'|| name,null ||  'aaaa' from user\_info

3、拼接函数里所有的字符串参数，忽略空字符串

select concat( id,NULL,':',\_name) from user\_info

4、第一个参数作为分隔符，拼接其他的几个参数，此处以！作为分隔符

select concat\_ws( '!',id,name) from user\_info