SQL 函数

 #############################################################################

• INSTR(C1,C2,I,J)  在一个字符串中搜索指定的字符,返回发现指定的字符的位置;

C1    被搜索的字符串
C2    希望搜索的字符串
I     搜索的开始位置,默认为1
J     出现的位置,默认为1

select instr("abcde",'b');

 结果是2，即在字符串“abcde”里面，字符串“b”出现在第2个位置。如果没有找到，则返回0；不可能返回负数。

#############################################################################

• CONCAT_WS(separator, str1, str2,...)：函数使用分隔符将两个或多个字符串连接成一个字符串。

SELECT CONCAT_WS('.', 'YiiBai', 'Com') full_doname

CONCAT_WS()函数在处理具有NULL值的输入字符串时，忽略NULL并且不在NULL值之间添加分隔符。

SELECT CONCAT_WS(',', 1, 2, NULL, NULL, 3);

•  concat(str1,str2,...,strn):把这n个字符连接起来

SELECT CONCAT('SQL', ' is', ' fun!');
SELECT CONCAT('SQL', ' ', 'is', ' ', 'fun!');

 #############################################################################

•  COLLECT_LIST()和COLLECT_SET()   (hive)

它们都是将分组中的某列转为一个数组返回，不同的是collect_list不去重而collect_set去重。

#创建表
create table t_visit_video (
    username char(5),
    video_name char(8)
);

INSERT INTO t_visit_video values
('张三','大唐双龙传'),
('李四','天下无贼'),
('张三','神探狄仁杰'),
('李四','霸王别姬'),
('李四','霸王别姬'),
('王五','机器人总动员'),
('王五','放牛班的春天'),
('王五','盗梦空间');

select username, collect_list(video_name) from t_visit_video group by username ;

 李四有重复值，霸王别姬看了两边。

select username, collect_set(video_name) from t_visit_video group by username;

李四的观看记录中霸王别姬只出现了一次，实现了去重效果。 

############################################################################# 

• DATEDIFF(data1,data2) :函数返回两个日期之间的时间。

SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate

############################################################################# 

• DATEADD (date_part , value , input_date ) ：将数字值添加到输入日期的指定日期部分，并返回修改后的值。

############################################################################# 

• LTRIM() ：从字符串中删除前面的空格

SELECT LTRIM(' SQL Tutorial') AS LeftTrimmedString;

• RTRIM()：函数从字符串中删除尾部（右边）空格。

SELECT RTRIM('SQL Tutorial ') AS RightTrimmedString;

 

•  TRIM()： 函数从字符串的开头（左边）或结尾（右边）删除空格字符或其他指定字符。

#############################################################################

•  REPLACE(string,old_string,new_string):函数将字符串中出现的所有子字符串替换为新的子字符串。

SELECT REPLACE('SQL Tutorial', 'T', 'M');

#############################################################################

• SUBSTRING(string,start,length) ：函数从字符串中第start位置开始提取长度为length的字符。

SELECT SUBSTRING('SQL Tutorial', 1, 3) AS ExtractString;

1）SUBSTRING(name,5,3) 截取name这个字段 从第五个字符开始 只截取之后的3个字符

SELECT SUBSTRING('成都融资事业部',5,3)

结果：事业部

2）SUBSTRING(name,3) 截取name这个字段 从第三个字符开始，之后的所有个字符

SELECT SUBSTRING('成都融资事业部',3)

结果：融资事业部

3）SUBSTRING(name, -4) 截取name这个字段的第 4 个字符位置（倒数）开始取，直到结束

SELECT SUBSTRING('成都融资事业部',-4)

结果：资事业部

4）SUBSTRING(name, -4，2) 截取name这个字段的第 4 个字符位置（倒数）开始取，只截取之后的2个字符

SELECT SUBSTRING('成都融资事业部',-4,2)

结果：资事

• substring_index()截取某个字符之前或之后的所有字符

1）substring_index('www.baidu.com', '.', 2) 截取第二个 '.' 之前的所有字符

SELECT substring_index('www.baidu.com', '.', 2)

结果：www.baidu

2）substring_index('www.baidu.com', '.', -2) 截取第二个 '.' （倒数）之后的所有字符

SELECT substring_index('www.baidu.com', '.', -2)

结果：baidu.com

 #############################################################################

• right():从字符串中提取3个字符（从右开始）

SELECT RIGHT('SQL Tutorial', 3) AS ExtractString

 

• LEFT() :从字符串中提取3个字符（从左开始）

SELECT LEFT('SQL Tutorial', 3) AS ExtractString

#############################################################################

• length():返回一个字符串的长度

SELECT length('W3Schools.cn')

#############################################################################

• lower():将文本转换为小写 

SELECT LOWER('SQL Tutorial is FUN!')

•  UPPER()：将文本转换为小写 

SELECT UPPER('SQL Tutorial is FUN!')