odps sql 字符处理函数

字符类型转换

隐式转换

类型转换涉及隐式转化和显示转化

显式转换

• 将DOUBLE类型转为BIGINT类型时，小数部分会被截断，例如cast(1.6 as BIGINT) = 1。
• 满足DOUBLE格式的STRING类型转换为BIGINT时，会先将STRING转换为DOUBLE，再将DOUBLE转换为BIGINT，因此，小数部分会被截断，例如cast(“1.6” as BIGINT) = 1。
• 满足BIGINT格式的STRING类型可以被转换为DOUBLE类型，小数点后保留一位，例如cast(“1” as DOUBLE) = 1.0。
• 日期类型转换时采用默认格式yyyy-mm-dd hh:mi:ss。

字符串处理函数

substr

• 命令格式：
  substr(string1, start_position[, length])
• 用途：
  返回字符串string1从start_position开始长度为length的子串。
  示例：

SELECT substr("abc", 2);
> bc

SELECT  substr("abc", 2, 1);
> b

instr

• 命令格式：
  instr(string1, string2[, start_position[, nth_appearance]])
• 用途：
  计算一个子串在字符串中的位置.
• 示例：

 select INSTR ('Tech on the net', 'e') 
 > 2 
select   INSTR ('Tech on the net', 'e', 1, 1)
> 2
select   INSTR ('Tech on the net', 'e', 1, 2)
>11 
select  INSTR ('Tech on the net', 'e', 1, 3)
>14

substr 和 instr结合

截取字符串中某个特定字符后的内容

ABCD_123 截取’_'后的数字

select substr('ABCD_123',instr('ABCD_123','_') + 1);
>123

截取 3_1.1.0_1 中的各部分

select substr ('3_1.1.0_1',1,instr('3_1.1.0_1','_',1,1)-1);
>3
select substr ('3_1.1.0_1',1,instr('3_1.1.0_1','_',1,2)-1);
>1.1.0
select substr('3_1.1.0_1',instr('3_1.1.0_1','_',1,2) + 1);
>1 

split_part

• 命令格式：

split_part(string, separator, start[, end])

• 用途：
  拆分字符串，返回指定的部分

同样是截取 3_1.1.0_1 各部分，用split_part更方便

select SPLIT_PART('3_1.1.0_1','_',1)
>3
select SPLIT_PART('3_1.1.0_1','_',2)
>1.1.0
select SPLIT_PART('3_1.1.0_1','_',3)
>1

replace 用法

• 替换字段内某部分内容
  
  replace(原字符串,‘要被替换掉的字符串’,‘用来替换的字符串’)

   select replace('bby_10001','bby_','ww')--->ww_10001
   
   select  replace(a.score,"a_","b_") AS delar_phone   from score2 as a

• 删除字段某部分内容
  
  replace(原字符串,‘被替换的部分字符串’,’’)

 select replace('bby_10001','bby_','')--->10001
 
 select  replace(a.score,' ','') AS delar_phone   from score2 as a--  删除空格

字符串拼接函数

concat

• 命令格式：
  concat(string A, string B…)

参数说明：
● A,B等为string类型，若输入为bigint, double, datetime类型会隐式转换为string后参与运算，其它类型报异常。
返回值：
string

• 用途：
  返回值是将参数中的所有字符串连接在一起的结果。
• 备注：
  如果没有参数或者某个参数为NULL，结果均返回NULL
  concat(), concat(null, ‘a’), concat(‘a’, null, ‘b’)返回值都是NULL。

在原有字符串后增加字符串

CONCAT('原字符串','','要增加的字符串')
SELECT CONCAT('bby_10001','','s');   ---->bby_10001s

对一列进行修改时：
select concat(列名,'','要增加的字符串') from 表名;

在作连接时  a.id=concat(表名.列名,'','要增加的字符串');

对原表进行更新时update table_name set 列名=CONCAT('原字符串','','要增加的字符串');
 

在原有字符串前增加字符串

CONCAT('要增加的字符串','原字符串')
SELECT CONCAT('s','bby_10001');--------------------->sbby_10001
 
对一列进行修改时：
select concat('要增加的字符串',列名) from 表名;

在作连接时  a.id=concatt('要增加的字符串',列名);

对原表进行更新时update table_name set 列名=CONCAT('要增加的字符串',列名);

在原有字符串前后加，

CONCAT(',','原有字符串',',')
SELECT CONCAT(',','bby_10001',',');   ---->,bby_10001s,

对一列进行修改时：
select concat(',','原有字符串',',') from 表名;

在作连接时  a.id=concatt(',','原有字符串',',');

对原表进行更新时update table_name set 列名=CONCATt(',','原有字符串',',');

create table `test`.`score2`(
`id` int(11) not null auto_increment,
`score`  int(10) null DEFAULT'0',
PRIMARY key(`id`)
)ENGINE=INNODB auto_increment=1;

concat_ws

• 命令格式：
  concat_ws(separator, string,string,string…)
• 用途：
  将string用指定的spearator拼接起来
  (将不同的列用指定分隔符拼接起来）

select concat_ws(',', 'abc', 'def', 'gh');
> abc,def,gh

SELECT  concat_ws(",",passengerid,survived)
FROM    train
;

wm_concat

• 命令格式：
  wm_concat(separator, string)
• 用途：
  用指定的spearator做分隔符，做字符串类型的SUM操作。
  (将某个列的值用指定的分隔符拼接起来）
  参数说明：
  ● separator，string类型常量，分隔符。其他类型或非常量将引发异常。
  ● string，string类型，若输入为bigint, double, datetime类型会隐式转换为string后参与运算，其它类型报异常。
  返回值：
  以separator分隔的字符串。
  备注：
  对语句SELECT wm_concat(‘,’,name) from table;若table为空集合，这条语句返回{NULL}。

SELECT wm_concat(",",passengerid)
FROM train
;

删除指定字符

TRIM

• 删除指定首字符

SELECT TRIM(LEADING ',' FROM ',barxxx');---------> 'barxxx' 

SELECT TRIM(LEADING ',' FROM a.score) from score2 as a

• 删除指定尾字符

SELECT TRIM(TRAILING ',' FROM 'barxxyz,'); -----------> 'barxxyz' 

SELECT TRIM(TRAILING ',' FROM a.score) from score2 as a

• 删除指定首位字符

SELECT TRIM(BOTH ',' FROM ',bar,'); -----------> 'bar' 

SELECT TRIM(BOTH ',' FROM ' ,bar, '); -----------> ',bar,' -- 这样是不成功的，想想为什么
SELECT trim(both ',' from TRIM(BOTH ' ' FROM ' ,bar, '));  ---------->'bar'

SELECT TRIM(TRAILING ',' FROM a.score) from score2 as a

删除空格

• ltrim：去除左空格

LTRIM(str) -- 去除左空格
SELECT LTRIM(' barbar'); -----------> 'barbar' 

select ltrim(a.score)  from score2 as a

• rtrim：去除右空格

RTRIM(str) -- mysql中的去除右空格 
SELECT RTRIM('barbar ');-------------- -> 'barbar' 

select rtrim(a.score)  from score2 as a

• trim:删除左右空格

SELECT TRIM(' bar ');------------------> 'bar' 

select ltrim(rtrim(a.score)) 
select trim(both ' ' from a.score)  from score2 as a

`

参考资料：
https://help.aliyun.com/document_detail/57792.html?spm=a2c4g.11186623.6.681.33b9a9b0FIgwyd