в 13:46, 26 мая 2010

2010-05-26T13:46:16Z

Admin: 1 версия

2010-05-26T10:22:19Z

1 версия

Новая страница

== A SELECT statement that uses the LEFT, RIGHT, and SUBSTRING functions==



<source lang="sql">
4>
5>
6> create table Bankers(
7> BankerID Integer,
8> BankerName VARCHAR(20),
9> BankerContactLName VARCHAR(20),
10> BankerContactFName VARCHAR(20),
11> BankerCity VARCHAR(20),
12> BankerState VARCHAR(20),
13> BankerZipCode VARCHAR(20),
14> BankerPhone VARCHAR(20)
15> )
16> GO
1>
2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111");
3> GO
(1 rows affected)
1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222");
2> GO
(1 rows affected)
1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333");
2> GO
(1 rows affected)
1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444");
2> GO
(1 rows affected)
1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555");
2> GO
(1 rows affected)
1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666");
2> GO
(1 rows affected)
1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777");
2> GO
(1 rows affected)
1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888");
2> GO
(1 rows affected)
1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999");
2> GO
(1 rows affected)
1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000");
2> GO
(1 rows affected)
1>
2>
3>
4> Select BankerName, BankerContactLName + ", " + LEFT(BankerContactFName, 1)
5> + "." AS ContactName, RIGHT(BankerPhone, 8) AS Phone
6> FROM Bankers
7> WHERE SUBSTRING(BankerPhone, 2, 3) = 559
8> ORDER BY BankerName
9> GO
Msg 245, Level 16, State 1, Server J\SQLEXPRESS, Line 4
Conversion failed when converting the varchar value "11-" to data type int.
1>
2> drop table Bankers;
3> GO
1>
2></source>

== Get the last name==



<source lang="sql">
3> DECLARE @FullName VarChar(25)
4> SET @FullName = "George Washington"
5> SELECT SUBSTRING(@FullName, CHARINDEX(" ", @FullName) + 1, LEN(@FullName))
6> GO
-------------------------
Washington
(1 rows affected)</source>

== Return first name==



<source lang="sql">
3> DECLARE @FullName VarChar(25)
4> SET @FullName = "George Washington"
5> -- Return first name:
6> SELECT SUBSTRING(@FullName, 1, CHARINDEX(" ", @FullName) - 1)
7> -- ^String ^Start ^Returns space index ^Don"t include space
8> GO
-------------------------
George
(1 rows affected)</source>

== Returning a Chunk of an Expression==



<source lang="sql">
4>
5> DECLARE @BankAccountNumber char(14)
6> SET @BankAccountNumber = "1111-1111-1111"
7> SELECT "XXXX-" + SUBSTRING(@BankAccountNumber, 6,4) + "-XXXX" Masked_BankAccountNumber
8> GO
Masked_BankAccountNumber
------------------------
XXXX-1111-XXXX
(1 rows affected)
1></source>

== select SUBSTRING("(1212", 7, 8)==



<source lang="sql">
2> select SUBSTRING("(559) 555-1212", 7, 8)
3> GO
--------
555-1212
(1 rows affected)</source>

== SUBSTRING(FullName, CHARINDEX(" ", FullName) + 1, LEN(FullName))==



<source lang="sql">
3> CREATE TABLE #MyNames (FullName VarChar(50))
4> GO
1> INSERT INTO #MyNames (FullName) SELECT "Fred
2~ INSERT INTO #MyNames (FullName) SELECT "Wilma
3> INSERT INTO #MyNames (FullName) SELECT "Barney
4~ INSERT INTO #MyNames (FullName) SELECT "Betty
5> INSERT INTO #MyNames (FullName) SELECT "George
6~ INSERT INTO #MyNames (FullName) SELECT "Jane
7>
8> SELECT
9> SUBSTRING(FullName, 1, CHARINDEX(" ", FullName) - 1) AS FirstName
10> , SUBSTRING(FullName, CHARINDEX(" ", FullName) + 1, LEN(FullName)) AS LastName
11> FROM #MyNames
12> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
FirstName LastName
-------------------------------------------------- --------------------------------------------------
Fred
INSERT INTO #MyNames (FullName) SELECT
Barney
INSERT INTO #MyNames (FullName) SELECT
George
INSERT INTO #MyNames (FullName) SELECT
(3 rows affected)
1>
2>
3> drop table #MyNames;
4> GO
1></source>

== SUBSTRING is used to retrieve part of a string from another string. The syntax for the function is as follows:==



<source lang="sql">
SUBSTRING(string_to_remove_string_from, start_position, length)
6> SELECT SUBSTRING("sqle sqle",2,3)
7> GO
------
ava
(1 rows affected)</source>

== Substring with variable==



<source lang="sql">
4> DECLARE @FullName VarChar(25)
5> SET @FullName = "George Washington"
6> SELECT SUBSTRING(@FullName, 4, 6)
7> GO
------
rge Wa
(1 rows affected)</source>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL Tutorial/String Functions/SUBSTRING - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия