AD

Sql summary of some useful

1: replace the first parameter to your function string, second parameter you want to replace part of the third parameter was asked to replace what you
select replace ('lihan', 'a', 'b')

-----------------------------
lihbn

(The number of rows affected 1 rows)
================================================== =======
2: substring function, you string the first argument, is to begin replacing the second position, replace the end of the third position
select substring ('lihan', 0,3);
-----
li

(The number of rows affected 1 rows)
================================================== =======
3: charindex function the first argument you want to find the char, the second parameter is the string you are looking for a return parameter in the parameter two position
select charindex ('a', 'lihan')
-----------
4

(The number of rows affected 1 rows)

================================================== =========
4: ASCII function returns the character expression in the left-most characters of the ASCII code value.
select ASCII ('lihan')
-----------
108

(The number of rows affected 1 rows)

================================================== ==============
5: nchar function defined by the Unicode standard, return an integer code with the specified Unicode character.
Parameter is between 0 and 65535 positive integer. If you specify a value outside this range will return NULL.
select nchar (3213)
----
unicode character

(The number of rows affected 1 rows)

================================================== =======
6: soundex
Returns a four character code (SOUNDEX), used to assess the similarity of two strings.
SELECT SOUNDEX ('lihan'), SOUNDEX ('lihon');
----- -----
L546 L542

(The number of rows affected 1 rows)
================================================== =======
7: char
Parameters between 0 and 255 integers. If the integer expression is not within this range will return a NULL value.
SELECT char (125)
----
}

(The number of rows affected 1 rows)

================================================== ========
8: str function the first argument must be numeric, the second parameter into account the position of char type, location of return is less than the parameter a *, is greater than the right-aligned
SELECT str (12345,3)
----
***

(The number of rows affected 1 rows)

SELECT str (12345,12)
------------
12345

(The number of rows affected 1 rows)
================================================== =========
9: difference function returns an integer value indicating the SOUNDEX values ​​of two character expressions of the difference between.
The integer returned is the same SOUNDEX value of the number of characters. Return value ranging from 0 to 4: 0 indicates little different or completely different, 4, said almost the same or identical.
SELECT difference ('lihan', 'liha')
-----------
3

(The number of rows affected 1 rows)

================================================== ================
10: stuff function (four parameters)
Function string into another string. It is the first string in a specified length from the beginning of the deleted character; and then a second string into the first start of the string.
SELECT stuff ('lihan', 2,3, 'lihan')
--------
llihann

(The number of rows affected 1 rows)
================================================== =============

11: left function returns the leftmost N characters, determined by the parameters
select left ('lihan', 4)
-----
liha

(The number of rows affected 1 rows)
================================================== ==============

12 right function returns the rightmost N characters, determined by the parameters
select right ('lihan', 4)
-----
ihan

(The number of rows affected 1 rows)
================================================== ==============

13: replicate the function I think is to copy the parameters of the second parameter is a
select replicate ('lihan', 4)
--------------------
lihanlihanlihanlihan

(The number of rows affected 1 rows)

================================================== ==============

14: len function returns the length of the parameter
select len ​​('lihan')
-----------
5

(The number of rows affected 1 rows)

================================================== ==============
15: reverse function to reverse a string
select reverse ('lihan')
-----
nahil

(The number of rows affected 1 rows)

================================================== ===============

16: lower and upper case conversion function parameters
select lower (upper ('lihan'))
--------------------
lihan

(The number of rows affected 1 rows)

================================================== ==================

17: ltrim and rtrim functions remove spaces left and right spaces
select ltrim ('lihan')
--------------------------
lihan

(The number of rows affected 1 rows)
select rtrim ('lihan')
---------
lihan

(The number of rows affected 1 rows)
标签: parameters, character code, similarity, integers, positive integer, ascii code, unicode character, integer expression, integer code, third position, soundex
分类: Database
时间: 2010-08-31

相关文章

  1. oracle - sql Summary

    1, sql summary First, repeat the operation check - Where conditions have distinct systemdicid as a unique iden ...
  2. Reposted elsewhere - by analyzing the SQL statement execution plan optimization of SQL (summary)

    By analyzing the SQL statement execution plan optimization of SQL (summary) DBA did almost 7 years, and sentim ...
  3. SQL Summary Notes (1)

    Sql old school for a long time, but recently has been using mysql, although almost all, but here still have to ...
  4. Efficient SQL Summary (change)

    Not only will we have to do to write SQL, but also achieve excellent performance to write SQL, the following i ...
  5. postgresql sql Summary

    [Size = x-small] // Update expired update capture set flag=1 where (extract (epoch from now())::bigint-extract ...
  6. Database sql Summary

    A year ago, in the school database system overview of this course, but the class just heard some less, and no ...
  7. HTZProject items used in the SQL summary

    The following SQL statement only for SQL Server: 1. Delete a view Generally use the following SQL statement: & ...
  8. SQL Summary: Inner join on, left join on, right join on detailed use

    1 Theory As long as there are two tables match the values ​​of public fields, it will be two records in the ta ...
  9. oracle according to the master association table foreign key found Sql Summary

    According to identified primary and foreign keys associated with the assembly sql select a.constraint_name, a. ...
  10. More practical sql summary

    / / Can be used to query field is blank or null fields 1.select * from International_Bailmanager where (mercha ...
  11. Summary SQL statement used

    Reprinted: http://www.talentdigger.cn/home/space.php?uid=3821&do=blog&id=19384 SQL Summary DML, DDL, D ...
  12. Ultra-detailed summary of SQL commonly used functions

    Sql Summary of Sql - commonly used functions A string function is the most widely used kind of oracle function ...
  13. PL / SQL study concluded

    PL / SQL Summary 1 declare variables and assign values to variables -- Declare variables declare uname varchar ...
  14. 仿orm自动生成分页SQL分享

    平时接触的数据库有sql2000-2008,Oracle,SQLite . 分页逻辑,Oracle和SQLite相对好写,就SQL事多,Sql2000下只能用top,排序2次,而Sql2005+就可以使用ROW_NUMB ...
  15. C#的SQL操作类实例

    这篇文章主要介绍了C#的SQL操作类实例,涉及到针对数据库的常用操作,在进行C#数据库程序设计中非常具有实用价值,需要的朋友可以参考下 本文实例讲述了C#的SQL操作类,分享给大家供大家参考.具体方法如下: using ...
  16. c#实现用SQL池,多线程定时批量执行SQL语句的方法

    构建SQL池,分离业务逻辑层和数据访问层,让业务逻辑层从低效的数据库操作解脱,以提高系统整体性能 在实际项目开发中,业务逻辑层的处理速度往往很快,特别是在开发Socket通信服务的时候,网络传输很快,但是一旦加上数据库操 ...
  17. Outsourcing for small to write a database access base class

    //-- Name :SQL Database access base class //-- Function : General ado.net principle , Data packing or North bo ...
  18. For the rapid development of data access class 1.1

    //-- Name :SQL Database access base class //-- Function : General ado.net principle , Data packing or North bo ...
  19. oracle analyse function CLASSIC

    Description: 1) PLSQL development notes and summary collected from http://www.blogjava.net/cheneyfree/ 2) Anal ...