select ascii('a') --ascii值 97
select char(97) --取字符 a
select db_id('dbname') --get db id
--比如select db_id('iqdemo') 0
select db_name() --get current database name 'iqdemo'
select isnull(price,0) -- nvl(price,0) in Oracle
select left('abcdef', 2) =>ab
select datalength('中国') => 6 # return the number of bytes(UTF-8)
select len('中国') => 2 # return the number of character
select char_length('中国') => 2 # return the number of character
select ltrim(' 123') => “123″
select next_identity ('t1') # equal to select seq.nextval() from dual in Oracle
select object_id('zhang') => 192000684
select replicate('abcd', 3) => abcdabcdabcd
select right('abcde', 3) => cde
select round(123.4545, 2) => 123.4500
select round(123.45, -2) => 100.00
select stuff('abc', 2, 3, 'xyz') => axyz --stuff 填充
select stuff('abcdef', 2, 3, null) => aef
select stuff('abcdef', 2, 3, ' ') => a ef
select str_replace('cdefghdefi','def','yyy') => cyyyghyyyi
select substring('abcdef', 3, 3) => cde
select suser_name()=>DBA
select user=>DBA
select user_name() => DBA
select suser_id() => 1
日期显示格式:
select getdate() #mon dd yyyy hh:mm AM (or PM)
select convert(varchar(30), getdate(), 109) #mon dd yyyy hh:mm:ss.sss AM (or PM)
select convert(varchar(15), getdate(),108) #hh:mm:ss
(DBA)> select getdate()
getdate()
-----------------------
2014-03-06 10:54:01.211
(DBA)> select convert(varchar(30), getdate(), 109)
getdate()
------------------------------
Mar 06 2014 10:54:06:720AM
(DBA)> select convert(varchar(15), getdate(),108)
getdate()
---------------
10:54:10
select char(97) --取字符 a
select db_id('dbname') --get db id
--比如select db_id('iqdemo') 0
select db_name() --get current database name 'iqdemo'
select isnull(price,0) -- nvl(price,0) in Oracle
select left('abcdef', 2) =>ab
select datalength('中国') => 6 # return the number of bytes(UTF-8)
select len('中国') => 2 # return the number of character
select char_length('中国') => 2 # return the number of character
select ltrim(' 123') => “123″
select next_identity ('t1') # equal to select seq.nextval() from dual in Oracle
select object_id('zhang') => 192000684
select replicate('abcd', 3) => abcdabcdabcd
select right('abcde', 3) => cde
select round(123.4545, 2) => 123.4500
select round(123.45, -2) => 100.00
select stuff('abc', 2, 3, 'xyz') => axyz --stuff 填充
select stuff('abcdef', 2, 3, null) => aef
select stuff('abcdef', 2, 3, ' ') => a ef
select str_replace('cdefghdefi','def','yyy') => cyyyghyyyi
select substring('abcdef', 3, 3) => cde
select suser_name()=>DBA
select user=>DBA
select user_name() => DBA
select suser_id() => 1
日期显示格式:
select getdate() #mon dd yyyy hh:mm AM (or PM)
select convert(varchar(30), getdate(), 109) #mon dd yyyy hh:mm:ss.sss AM (or PM)
select convert(varchar(15), getdate(),108) #hh:mm:ss
(DBA)> select getdate()
getdate()
-----------------------
2014-03-06 10:54:01.211
(DBA)> select convert(varchar(30), getdate(), 109)
getdate()
------------------------------
Mar 06 2014 10:54:06:720AM
(DBA)> select convert(varchar(15), getdate(),108)
getdate()
---------------
10:54:10