SQL内置函数大全

SparkSQL内置函数--whenotherwise [root@centos00 ~]$ cd hadoop-2.6.0-cdh5.14.2/[root@centos00 hadoop-2.6.0-cdh5.14.2]$ sbin/hadoop-daemon.sh start namenode[root@centos00 hadoop-2.6.0-cdh5.14.2]$ sbin/hadoop-daemon.sh start datanode[root@centos00 hadoop-2.6.0-cdh5.14.2]$ sbin/yarn-daemon.sh start resourcemanager[root@centos00 ~]$ cd /opt/cdh5.14.2/hive-1.1.0-cdh5.14.2/[root@centos00 hive-1.1.0-cdh5.14.2]$ bin/hive --service metastore &[root@centos00 ~]$ cd /opt/cdh5.14.2/spark-2.2.1-cdh5.14.2/[root@centos00 spark-2.2.1-cdh5.14.2]$ sbin/start-master.sh[root@centos00 spark-2.2.1-cdh5.14.2]$ sbin/start-slaves.shscala> spark.sql("create table mydemo(id int, name string, gender string)")res0: org.apache.spark.sql.DataFrame = []scala> spark.sql("insert into mydemo values(1, 'Jack', 'M'),(2, 'Judy', 'F')")res1: org.apache.spark.sql.DataFrame = []scala> spark.sql("select * from mydemo").show(false)+---+----+------+|id |name|gender|+---+----+------+|1 |Jack|M ||2 |Judy|F |+---+----+------+scala> var ds = spark.table("mydemo")ds: org.apache.spark.sql.DataFrame = [id: int, name: string ... 1 more field]scala> ds = ds.withColumn("flag",when($"gender" === "M",true).otherwise(false))ds: org.apache.spark.sql.DataFrame = [id: int, name: string ... 2 more fields]scala> ds.show(false)+---+----+------+-------+|id |name|gender|flag|+---+----+------+-------+|1 |Jack|M |true ||2 |Judy|F |false |+---+----+------+-------+scala> var df = Seq(| (1, "regular"),| (2, "Unknown")).toDF("id", "size")df: org.apache.spark.sql.DataFrame = [id: int, size: string]scala> df = df.withColumn("flag",when($"size".isin(Array[String]("regular"):_*),0D).otherwise($"size"))df: org.apache.spark.sql.DataFrame = [id: int, size: string ... 1 more field]scala> df.show(false)+---+-------+-------+|id |size |flag |+---+-------+-------+|1 |regular|0.0 ||2 |Unknown|Unknown|+---+-------+-------+。

SQL的⼀些内置函数CHARINDEX⽤于查找字符串A中是否包含字符串B。

返回值是字符串B在字符串A中的位置（计数从1开始），若B中没有A,则返回0。

基本语法如下：CHARINDEX ( expressionToFind , expressionToSearch , start_location)1. expressionToFind ：⽬标字符串，就是想要找到的字符串，最⼤长度为8000 。

2. expressionToSearch ：⽤于被查找的字符串。

3. start_location：开始查找的位置，可为空（为空时默认从第⼀位开始查找）返回值是从⾸位字符开始计数。

案例如下：--查询字母d在字符串中的位置select charindex('d','abcdefgh') as A,--默认从第⼀位开始检查charindex('d','abcdefgh',3) as B,--从第三位开始检查charindex('d','abcDefgh' COLLATE Latin1_General_CS_AS) as C,--区分⼤⼩写charindex('d','abcDefgh'COLLATE Latin1_General_CI_AS) as D--不区分⼤⼩写PATINDEX⽤于查找字符串A中是否包含字符串B(⽀持模糊查询)。

返回值是字符串B在字符串A中的位置（计数从1开始），若B中没有A,则返回0。

基本语法如下：PARTINDEX ( expressionToFind , expressionToSearch)1. expressionToFind ：⽬标字符串，就是想要找到的字符串，最⼤长度为8000 。

2. expressionToSearch ：⽤于被查找的字符串。

返回值是从⾸位字符开始计数。

SparkSQL内置函数Spark SQL内置函数官⽹API：平常在使⽤mysql的时候，我们在写SQL的时候会使⽤到MySQL为我们提供的⼀些内置函数，如数值函数：求绝对值abs()、平⽅根sqrt()等，还有其它的字符函数、⽇期函数、聚合函数等等。

使我们利⽤这些内置函数能够快速实现我们的业务逻辑。

在SparkSQL⾥其实也为我们提供了近两百多种内置函数，我们通过import org.apache.spark.sql.functions._导⼊内置函数包，来使⽤。

也可以在SQL语句中直接使⽤。

SparkSQL内置函数分类：聚合函数、集合函数、⽇期函数、数学函数、混杂函数、⾮聚合函数、排序函数、字符串函数、UDF函数和窗⼝函数这10类函数。

1 内置函数的使⽤使⽤内置函数的⽅式有两种，⼀种是通过编程的⽅式的使⽤，另⼀种是通过SQL的⽅式使⽤。

例如：我们有如下数据，想要使⽤SparkSQL内置函数lower()来将名字全部转为⼩写+----+---+-----------+|name|age| phone|+----+---+-----------+|Ming| 20|155********||hong| 19|132********|| zhi| 21|155********|+----+---+-----------+以编程的⽅式使⽤内置函数import org.apache.spark.sql.functions._df.select(lower(col("name")).as("name"), col("age"), col("phone")).show()以SQL的⽅式使⽤df.createOrReplaceTempView("people")spark.sql("select lower(name) as name,age,phone from people").show()2 UDF函数的使⽤有的时候，SparkSQL提供的内置函数⽆法满⾜我们的业务的时候，我们可以使⽤过UDF函数来⾃定义我们的实现逻辑。

SQLServer内置函数类型转换函数cast和convert--cast⼀般更容易使⽤,convert的优点是可以格式化⽇期和数值select CAST('123'as int) -- 123select CONVERT(int, '123') -- 123select CAST(123.4as int) -- 123select CONVERT(int, 123.4) -- 123select CAST('123.4'as int) --失败select CONVERT(int, '123.4') --失败select CAST('123.4'as decimal) -- 123select CONVERT(decimal, '123.4') -- 123select CAST('123.4'as decimal(9,2)) -- 123.40select CONVERT(decimal(9,2), '123.4') -- 123.40declare @Num moneyset @Num = 1234.56select CONVERT(varchar(20), @Num, 0) -- 1234.56select CONVERT(varchar(20), @Num, 1) -- 1,234.56select CONVERT(varchar(20), @Num, 2) -- 1234.5600字符函数--len()⽤来计算字符串的长度select LEN('123456') --6select LEN('⼀⼆三') --3select LEN('⼀23') --3--lower()⽤来将⼀个字符串转换为⼩写,upper()⽤来将⼀个字符串转换为⼤写select lower('ABC') --abcselect upper('abc') --ABC--ltrim()⽤来将⼀个字符串左侧的空格去掉,rtrim()⽤来将⼀个字符串右侧的空格去掉select ltrim(' AAA') --AAAselect rtrim('AAA ') --AAA--substring(string,start_position,length) 可以从任意位置取任意长度的⼦字符串select substring('HelloWorld!',6,6) ---World!--left(string,length) 从左侧开始取⼦字符串select left('HelloWorld!' ,5) --Hello--right(string,length)从右侧开始取⼦字符串select right('HelloWorld!' ,6) --World!--replace（string,要被替换的字符串，替换的字符串）select replace('aaabbbcccdddaaabbbcccddd','aa','11') --11abbbcccddd11abbbcccddd--reverse(string_expression)返回字符串值的逆向值select reverse('abc') --cba--删除指定长度的字符，并在指定的起点处插⼊另⼀组字符--stuff(character_expression , start , length ,character_expression)select stuff('aaabbbcccdddaaabbbcccddd',4,6,'222333') --aaa222333dddaaabbbcccddd--以指定的次数重复字符串值--replicate(string_expression ,integer_expression)select replicate('123',5) --123123123123123--返回字符串中指定表达式的开始位置--charindex(expression1 ,expression2 , start_location )或charindex(expression1 ,expression2 ）--expression1在expression2 中的开始位置select charindex('H','elloHWorld') --5。

SPARK-SQL内置函数之时间⽇期类⼀、获取当前时间1.current_date获取当前⽇期2018-04-092.current_timestamp/now()获取当前时间2018-04-09 15:20:49.247⼆、从⽇期时间中提取字段1.year,month,day/dayofmonth,hour,minute,secondExamples:> SELECT day('2009-07-30'); 302.dayofweek (1 = Sunday, 2 = Monday, ..., 7 = Saturday),dayofyearExamples:> SELECT dayofweek('2009-07-30'); 5Since: 2.3.03.weekofyearweekofyear(date) - Returns the week of the year of the given date. A week is considered to start on a Monday and week 1 is the first week with >3 days.Examples:> SELECT weekofyear('2008-02-20'); 84.trunc截取某部分的⽇期，其他部分默认为01第⼆个参数 ["year", "yyyy", "yy", "mon", "month", "mm"]Examples:> SELECT trunc('2009-02-12', 'MM');2009-02-01> SELECT trunc('2015-10-27', 'YEAR');2015-01-015.date_trunc ["YEAR", "YYYY", "YY", "MON", "MONTH", "MM", "DAY", "DD", "HOUR", "MINUTE", "SECOND", "WEEK", "QUARTER"]Examples:> SELECT date_trunc('2015-03-05T09:32:05.359', 'HOUR'); 2015-03-05T09:00:00Since: 2.3.06.date_format将时间转化为某种格式的字符串Examples:> SELECT date_format('2016-04-08', 'y'); 2016三、⽇期时间转换1.unix_timestamp返回当前时间的unix时间戳Examples:> SELECT unix_timestamp(); 1476884637> SELECT unix_timestamp('2016-04-08', 'yyyy-MM-dd'); 14600412002.from_unixtime将时间戳换算成当前时间，to_unix_timestamp将时间转化为时间戳Examples:> SELECT from_unixtime(0, 'yyyy-MM-dd HH:mm:ss'); 1970-01-01 00:00:00>SELECT to_unix_timestamp('2016-04-08', 'yyyy-MM-dd'); 14600412003.to_date/date将字符串转化为⽇期格式，to_timestamp（Since: 2.2.0）> SELECT to_date('2009-07-30 04:17:52'); 2009-07-30> SELECT to_date('2016-12-31', 'yyyy-MM-dd'); 2016-12-31> SELECT to_timestamp('2016-12-31 00:12:00'); 2016-12-31 00:12:004.quarter 将1年4等分(range 1 to 4)Examples:> SELECT quarter('2016-08-31'); 3四、⽇期、时间计算1.months_between两个⽇期之间的⽉数months_between(timestamp1, timestamp2) - Returns number of months between timestamp1 and timestamp2.Examples:> SELECT months_between('1997-02-28 10:30:00', '1996-10-30'); 3.949596772. add_months返回⽇期后n个⽉后的⽇期Examples:> SELECT add_months('2016-08-31', 1); 2016-09-30st_day(date),next_day(start_date, day_of_week)Examples:> SELECT last_day('2009-01-12'); 2009-01-31> SELECT next_day('2015-01-14', 'TU'); 2015-01-204.date_add,date_sub(减)date_add(start_date, num_days) - Returns the date that is num_days after start_date.Examples:> SELECT date_add('2016-07-30', 1); 2016-07-315.datediff（两个⽇期间的天数）datediff(endDate, startDate) - Returns the number of days from startDate to endDate.Examples:> SELECT datediff('2009-07-31', '2009-07-30'); 16.关于UTC时间to_utc_timestampto_utc_timestamp(timestamp, timezone) - Given a timestamp like '2017-07-14 02:40:00.0', interprets it as a time in the given time zone, and renders that time as a timestamp in UTC. For example, 'GMT+1' would yield '2017-07-14 01:40:00.0'.Examples:> SELECT to_utc_timestamp('2016-08-31', 'Asia/Seoul'); 2016-08-30 15:00:0from_utc_timestampfrom_utc_timestamp(timestamp, timezone) - Given a timestamp like '2017-07-14 02:40:00.0', interprets it as a time in UTC, and renders that time as a timestamp in the given time zone. For example, 'GMT+1' would yield '2017-07-14 03:40:00.0'.Examples:> SELECT from_utc_timestamp('2016-08-31', 'Asia/Seoul'); 2016-08-31 09:00:00。

Oracl e SQL 内置函数大全(一)整理时间[2005-10-14] 阅读次数[6224]导读:Oracle SQL 内置函数大全(一)Oracle SQL 内置函数大全(二)Oracle SQL 内置函数大全(三)________________________________________文字大小:【大】【中】【小】SQL中的单记录函数1.ASCII返回与指定的字符对应的十进制数;SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;A a ZERO SPACE--------- --------- --------- ---------65 97 48 322. 2.CHR给出整数,返回对应的字符;SQL> select chr(54740) zhao,chr(65) chr65 from dual;ZH C-- -赵A3. 3.CONCAT连接两个字符串;SQL> select concat('010-','88888888')||'转23' 高乾竞电话from dual; 高乾竞电话----------------010-********转234. 4.INITCAP返回字符串并将字符串的第一个字母变为大写;SQL> select initcap('smith') upp from dual;UPP-----Smith5. 5.INSTR(C1,C2,I,J)在一个字符串中搜索指定的字符,返回发现指定的字符的位置;C1 被搜索的字符串C2 希望搜索的字符串I 搜索的开始位置,默认为1J 出现的位置,默认为1(第一次?)SQL> select instr('oracle traning','ra',1,2) instring from dual;INSTRING---------96. 6.LENGTH返回字符串的长度;SQL> selectname,length(name),addr,length(addr),sal,length(to_char(sal))from .nchar_tst; NAME LENGTH(NAME) ADDR LENGTH(ADDR) SALLENGTH(TO_CHAR(SAL))------ ------------ ---------------- ------------ --------- --------------------高乾竞 3 北京市海锭区 6 9999.99 7。

Access常用内置SQL函数一、Access常用内置SQL函数---日期时间函数1.CDate说明:将字符串转化成为日期举例:select CDate("2008/4/5")2.Date说明:返回当前日期举例:select Date()3.DateAdd说明:将指定日期加上某个日期举例:select dateAdd("d",30,Date()),其中d可以换为yyyy或H等4.DateDiff说明:判断两个日期之间的间隔举例:select DateDiff("d","2008-5-1",now) 其中d可以换为yyyy或H等5.DatePart说明:返回日期的某个部分举例:select DatePart("d","2008-5-1")返回1,即1号,d也可以换为yyyy或m6.Day说明:返回日期的d部分,等同于datepart的d部分举例:select Day("2008-5-1")返回1,即1号7.Hour说明:返回日期的小时举例:select Hour("2008-5-1 18:25:14")返回18,即18点8.IsDate说明:判断是否是日期,是日期返回-1,不是日期返回0举例:select IsDate("2008-5-1 18:25:14")9.Minute 返回日期的分钟部分说明:返回日期的分钟部分举例:select Minute("2008-5-1 18:25:14")返回25,即25分10.Month说明:返回日期的月份部分举例:select Month("2008-5-1 18:25:14")返回5,即5月11.Now说明: 返回当前时间(年月日时分秒)举例:select Now()12.Second说明:返回日期的秒部分举例:select Second("2008-5-1 18:25:14")返回14,即14秒13.Time说明:返回当前的时间部分举例:select Time()14.weekday说明:返回某个日期的当前星期(星期天为1,星期一为2,星期二为3 ...) 举例:select weekday(now())15.Year说明:返回某个日期的年份举例:select Year("2008-5-1 18:25:14")返回2008,即2008年二、 Access常用内置SQL函数---文本字符串函数1.Asc说明:返回字母的Acsii值举例:select Asc("A")返回652.Chr说明:将ascii值转换到字符举例:select chr(65)返回"A"3.Format说明:格式化字符串举例:Select Format(now(),"yyyy-mm-dd")返回类似于"2008-04-03" Select Format(3/9,"0.00")返回0.334.InStr说明:查询子串在字符串中的第一个出现的位置,没有返回0举例:select Instr("abc","a") 返回15.LCase说明:返回字符串的小写形式举例:select LCase("ABC") 返回"abc"6.Left说明:左截取字符串举例:select Left("ABC",1) 返回"A"7.Len说明:返回字符串长度举例:select Len("ABC你好")返回58.LTrim说明:左截取空格举例:select LTrim(" 111") 返回"111"9.Mid说明:取得子字符串举例:select mid("abcd",1,2) 返回"ab"10.Right说明:右截取字符串举例:select Right("ABC",1) 返回"C"11.RTrim说明:右截取空格举例:select Right("ABC ") 返回"ABC"12.Space说明:产生空格举例:select Space(5) 返回5个空格13.StrComp说明:比较两个字符串是否内容一致(不区分大小写) 举例:select StrComp("abc","ABC")返回0select StrComp("abc","123")返回-114.Trim说明:截取字符串两头的空格举例:select Trim(" ABC ") 返回"ABC"15.UCase说明:将字符串转大写举例:select UCase("abc") 返回"ABC"三、 Access常用内置SQL函数---统计类函数1.Avg说明:取字段平均值2.Count说明:统计记录条数3.Max说明:取字段最大值4.Min说明:取字段最小值5.StDev说明:估算样本的标准差（忽略样本中的逻辑值和文本）6.StDevP说明:计算以参数形式（忽略逻辑值和文本）给出的整个样本总体的标准偏差7.Sum说明:计算字段的总合8.Var说明:估算样本方差（忽略样本中的逻辑值和文本）9.VarP说明:计算整个样本总体的方差（忽略样本总体中的逻辑值和文本）。

sql去掉空格的函数
SQL去掉空格的函数是指在sql中使用特定函数来去掉字符串中的
空格或其他空白字符。

SQL有很多内置函数可以用于清理字符串，其中
最常用的有RTRIM（），LTRIM（）和TRIM（）三个函数。

RTRIM（）函数用于从字符串的右端删除空格或其他空白字符。

它
可以接受一个参数，即要去掉空格的字符串，如果不传入参数，它会
默认从字符串右端开始去掉空格。

语法为：RTRIM（string1,
string2...）。

LTRIM（）函数用于从字符串的左端删除空格或其他空白字符，它
也可以接受一个参数，即要去掉空格的字符串，如果不传入参数，它
会默认从字符串左端开始去掉空格。

语法为：LTRIM（string1,
string2...）。

TRIM（）函数作用是从字符串的两端删除空格或其他空白字符，
它会将左侧空格和右侧空格同时去掉，这一点和前面两个函数不同。

语法为：TRIM（[leading|trailing] [removertarget] from
string1）。

以上三个函数都是sql内置函数，可以用来在sql语句中去掉字
符串中的空格和空白字符，由于各个数据库系统对 sql 的实现不一样，所以具体的函数细节也有所不同。

sql简体到繁体转化的内置函数SQL内置了一些函数用于简体中文转化为繁体中文，这些函数可以让我们更加便利地进行数据处理和分析。

其中主要包括以下的函数：
1. CHT_TO_CHS：将繁体中文转换为简体中文。

2. CHS_TO_CHT：将简体中文转换为繁体中文。

3. CONVERT：实现中文字符集之间的转换，包括简体中文和繁体
中文之间的转换。

例如，我们可以使用CHT_TO_CHS函数将繁体中文列转换为简体中
文列，如下所示：
SELECT CHT_TO_CHS(列名) FROM 表名;
这将返回一个新的列，其中所有的繁体中文将被转换为简体中文。

同样，我们可以使用CHS_TO_CHT函数将简体中文列转换为繁体中文列：SELECT CHS_TO_CHT(列名) FROM 表名;
需要注意的是，这些函数只适用于中文字符集，如果需要将其他
语言的文本转化为中文，需要采用其他方法。