Sql Server实用函数汇总

1. datalength函数返回用于表示任何表达式的字节数。LEN函数返回指定字符串表达式的字符数，其中不包含尾随空格。转载https://docs.microsoft.com/zh-cn/sql/t-sql/functions/datalength-transact-sql?view=sql-server-ver15
2. 浮点数转字符串STR(字段名称，数据长度，小数点位数)
3. replace (str (5,3),' ','0')  结果为005
4. merge into 功能等同于mysql 中replace into，如果表中有记录则更改否则插入
5. 对具有相同某个编号的数据进行字符串拼接

   Select
         A.maskid
       , A.maskname
       , A.schoolid
       , B.schoolname
       , STUFF((
             SELECT ',' + T.maskdetail
             FROM dbo.maskdetails T
             WHERE A.maskid = T.maskid
             FOR XML PATH('')), 1, 1, '') as maskdetail 
   FROM dbo.tblmask A
   JOIN dbo.school B ON B.ID = A.schoolid
   Group by  A.maskid
       , A.maskname
       , A.schoolid
       , B.schoolname

6. 列转行  https://stackoverflow.com/questions/16193152/sql-server-convert-select-a-column-and-convert-it-to-a-string

   declare @results varchar(500)
   
   select @results = coalesce(@results + ',', '') +  convert(varchar(12),col)
   from t
   order by col
   
   select @results as results

   select  stuff(list,1,1,'')
   from    (
           select  ',' + cast(col1 as varchar(16)) as [text()]
           from    YourTable
           for     xml path('')
           ) as Sub(list) --表名(列名)

7. COALESCE 是CASE与ISNULL结合