SQL Server/T-SQL Tutorial/Data Types/VARCHAR
Содержание
- 1 City name is Dallas
- 2 CONVERT(varchar(12), OrderDate, 111)
- 3 How to concatenate string data
- 4 How to format string data using literal values
- 5 How to include apostrophes in literal values
- 6 Performing String Concatenation
- 7 Replace the string "102" located at offset 9 (zero-based) with the string "one hundred and two"
- 8 Selecting the length of a varchar column.
- 9 The IN() Function matches a field to any number of values in a list.
- 10 To use a string literal or a date literal in a comparison, enclose it in quotes.
- 11 Use REPLICATE to fill a varchar type variable
- 12 VARCHAR(MAX)
- 13 Varchar type value pattern matching
City name is Dallas
<source lang="sql">
5> CREATE TABLE employee( 6> id INTEGER NOT NULL PRIMARY KEY, 7> first_name VARCHAR(10), 8> last_name VARCHAR(10), 9> salary DECIMAL(10,2), 10> start_Date DATETIME, 11> region VARCHAR(10), 12> city VARCHAR(20), 13> managerid INTEGER 14> ); 15> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3 2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4 3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5 4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6 5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7 6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8 7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9 8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9 9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected) 1> 2> 3> SELECT ID 4> FROM Employee 5> WHERE city = "Dallas" 6> GO ID
(0 rows affected) 1> 2> 3> 4> drop table employee; 5> GO</source>
CONVERT(varchar(12), OrderDate, 111)
<source lang="sql">
4> 5> CREATE TABLE Orders ( 6> OrderID int NOT NULL , 7> CustomerID nchar (5) NULL , 8> EmployeeID int NULL , 9> OrderDate datetime NULL , 10> RequiredDate datetime NULL , 11> ShippedDate datetime NULL , 12> ShipVia int NULL , 13> Freight money NULL DEFAULT (0), 14> ShipName nvarchar (40) NULL , 15> ShipAddress nvarchar (60) NULL , 16> ShipCity nvarchar (15) NULL , 17> ShipRegion nvarchar (15) NULL , 18> ShipPostalCode nvarchar (10) NULL , 19> ShipCountry nvarchar (15) NULL 20> ) 21> GO 1> 2> SELECT OrderDate, CONVERT(varchar(12), OrderDate, 111) AS "Converted" 3> FROM Orders 4> WHERE OrderID = 11050 5> GO OrderDate Converted
------------
(0 rows affected) 1> 2> drop table orders; 3> GO</source>
How to concatenate string data
<source lang="sql">
4> 5> create table Bankers( 6> BankerID Integer, 7> BankerName VARCHAR(20), 8> BankerContactLName VARCHAR(20), 9> BankerContactFName VARCHAR(20), 10> BankerCity VARCHAR(20), 11> BankerState VARCHAR(20), 12> BankerZipCode VARCHAR(20), 13> BankerPhone VARCHAR(20) 14> ) 15> 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> SELECT BankerCity, BankerState, BankerCity + BankerState 4> FROM Bankers 5> GO BankerCity BankerState
-------------------- ----------------------------------------
Vancouver BC VancouverBC New York DE New YorkDE Paris CA ParisCA Regina ER ReginaER Toronto YU TorontoYU Calgary TY CalgaryTY San Franc FG San FrancFG Orland PO OrlandPO Wisler PL WislerPL Ticker MN TickerMN (10 rows affected) 1> 2> drop table Bankers; 3> GO 1> 2></source>
How to format string data using literal values
<source lang="sql">
5> 6> 7> create table Bankers( 8> BankerID Integer, 9> BankerName VARCHAR(20), 10> BankerContactLName VARCHAR(20), 11> BankerContactFName VARCHAR(20), 12> BankerCity VARCHAR(20), 13> BankerState VARCHAR(20), 14> BankerZipCode VARCHAR(20), 15> BankerPhone VARCHAR(20) 16> ) 17> 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, 5> BankerCity + ", " + BankerState + " " + BankerZipCode AS Address 6> FROM Bankers 7> GO BankerName Address
---------------------------------------------------------------
ABC Inc. Vancouver, BC 11111 DEF Inc. New York, DE 22222 HJI Inc. Paris, CA 33333 QWE Inc. Regina, ER 44444 RTY Inc. Toronto, YU 55555 YUI Inc. Calgary, TY 66666 OIP Inc. San Franc, FG 77777 SAD Inc. Orland, PO 88888 DFG Inc. Wisler, PL 99999 GHJ Inc. Ticker, MN 00000 (10 rows affected) 1> 2> drop table Bankers; 3> GO 1> 2></source>
How to include apostrophes in literal values
<source lang="sql">
5> 6> 7> create table Bankers( 8> BankerID Integer, 9> BankerName VARCHAR(20), 10> BankerContactLName VARCHAR(20), 11> BankerContactFName VARCHAR(20), 12> BankerCity VARCHAR(20), 13> BankerState VARCHAR(20), 14> BankerZipCode VARCHAR(20), 15> BankerPhone VARCHAR(20) 16> ) 17> 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> SELECT BankerName + """s Address: ", 4> BankerCity + ", " + BankerState + " " + BankerZipCode 5> FROM Bankers 6> GO
---------------------------------------------------------------
ABC Inc."s Address: Vancouver, BC 11111 DEF Inc."s Address: New York, DE 22222 HJI Inc."s Address: Paris, CA 33333 QWE Inc."s Address: Regina, ER 44444 RTY Inc."s Address: Toronto, YU 55555 YUI Inc."s Address: Calgary, TY 66666 OIP Inc."s Address: San Franc, FG 77777 SAD Inc."s Address: Orland, PO 88888 DFG Inc."s Address: Wisler, PL 99999 GHJ Inc."s Address: Ticker, MN 00000 (10 rows affected) 1> 2> drop table Bankers; 3> GO</source>
Performing String Concatenation
<source lang="sql">
3> 4> CREATE TABLE employee( 5> id INTEGER NOT NULL PRIMARY KEY, 6> first_name VARCHAR(10), 7> last_name VARCHAR(10), 8> salary DECIMAL(10,2), 9> start_Date DATETIME, 10> region VARCHAR(10), 11> city VARCHAR(20) 12> ); 13> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto"); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city
---------- ---------- ------------ ----------------------- ---------- --------------------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto
(9 rows affected) 1> 2> 3> 4> SELECT "The " + 5> p.first_name + 6> " is from " + 7> CONVERT(varchar(25),p.city) + 8> "!" 9> FROM employee p; 10> GO
The Jason is from Vancouver! The Alison is from Utown! The James is from Paris! The Celia is from London! The Robert is from Newton! The Linda is from Calgary! The David is from New York! The James is from Regina! The Joan is from Toronto! (9 rows affected) 1> 2> 3> drop table employee; 4> GO</source>
Replace the string "102" located at offset 9 (zero-based) with the string "one hundred and two"
<source lang="sql">
7> IF OBJECT_ID("dbo.CustomerData") IS NOT NULL 8> DROP TABLE dbo.CustomerData; 9> GO 1> 2> CREATE TABLE dbo.CustomerData 3> ( 4> custid INT NOT NULL PRIMARY KEY, 5> txt_data VARCHAR(MAX) NULL, 6> ntxt_data NVARCHAR(MAX) NULL, 7> binary_data VARBINARY(MAX) NULL 8> ); 9> GO 1> 2> UPDATE dbo.CustomerData 3> SET txt_data.WRITE("one hundred and two", 9, 3) 4> WHERE custid = 102; 5> GO 1> 2> drop table CustomerData; 3> GO</source>
Selecting the length of a varchar column.
<source lang="sql">
6> CREATE TABLE publishers( 7> pub_id char(4) NOT NULL, 8> pub_name varchar(40) NULL, 9> city varchar(20) NULL, 10> state char(2) NULL, 11> country varchar(30) NULL DEFAULT("USA") 12> ) 13> GO 1> 2> 3> insert publishers values("1", "Publisher A", "Vancouver", "MA", "USA") 4> insert publishers values("2", "Publisher B", "Washington", "DC", "USA") 5> insert publishers values("3", "Publisher C", "Berkeley", "CA", "USA") 6> insert publishers values("4", "Publisher D", "New York", "NY", "USA") 7> insert publishers values("5", "Publisher E", "Chicago", "IL", "USA") 8> insert publishers values("6", "Publisher F", "Dallas", "TX", "USA") 9> insert publishers values("7", "Publisher G", "Vancouver", "BC", "Canada") 10> insert publishers values("8", "Publisher H", "Paris", NULL, "France") 11> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> 4> SELECT DATALENGTH(name) 5> FROM syscolumns 6> WHERE id = OBJECT_ID("publishers") 7> AND name = "pub_id" 8> 9> GO
12
(1 rows affected) 1> 2> drop table publishers; 3> GO 1></source>
The IN() Function matches a field to any number of values in a list.
<source lang="sql">
9> CREATE TABLE employee( 10> id INTEGER NOT NULL PRIMARY KEY, 11> first_name VARCHAR(10), 12> last_name VARCHAR(10), 13> salary DECIMAL(10,2), 14> start_Date DATETIME, 15> region VARCHAR(10), 16> city VARCHAR(20), 17> managerid INTEGER 18> ); 19> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3 2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4 3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5 4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6 5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7 6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8 7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9 8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9 9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected) 1> 2> 3> SELECT First_Name FROM Employee 4> WHERE City IN ("Toronto", "Vancouver", "New York"); 5> GO First_Name
Jason David Joan (3 rows affected) 1> 2> 3> 4> drop table employee; 5> GO 1> 2> 3> 4></source>
To use a string literal or a date literal in a comparison, enclose it in quotes.
<source lang="sql">
6> 7> 8> create table Bankers( 9> BankerID Integer, 10> BankerName VARCHAR(20), 11> BankerContactLName VARCHAR(20), 12> BankerContactFName VARCHAR(20), 13> BankerCity VARCHAR(20), 14> BankerState VARCHAR(20), 15> BankerZipCode VARCHAR(20), 16> BankerPhone VARCHAR(20) 17> ) 18> 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> select * from Bankers WHERE BankerName < "M" 3> GO BankerID BankerName BankerContactLName BankerContactFName BankerCity BankerState BankerZipCode BankerPhone
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
1 ABC Inc. Joe Smith Vancouver BC 11111 111-111-1111 2 DEF Inc. Red Rice New York DE 22222 222-222-2222 3 HJI Inc. Kit Cat Paris CA 33333 333-333-3333 9 DFG Inc. Sad Lee Wisler PL 99999 999-999-9999 0 GHJ Inc. Bit Lee Ticker MN 00000 000-000-0000
(5 rows affected) 1> 2> drop table Bankers; 3> GO 1> 2></source>
Use REPLICATE to fill a varchar type variable
<source lang="sql">
3> DECLARE @sql1 AS VARCHAR(8000), @sql2 AS VARCHAR(8000), @sql3 AS VARCHAR(8000); 4> SET @sql1 = "PRINT """; 5> SET @sql2 = REPLICATE("A", 7999) + "B"; 6> SET @sql3 = """;"; 7> EXEC(@sql1 + @sql2 + @sql3); 8> 9> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAB</source>
VARCHAR(MAX)
<source lang="sql">
2> IF OBJECT_ID("dbo.CustomerData") IS NOT NULL 3> DROP TABLE dbo.CustomerData; 4> GO 1> 2> CREATE TABLE dbo.CustomerData 3> ( 4> custid INT NOT NULL PRIMARY KEY, 5> txt_data VARCHAR(MAX) NULL, 6> ntxt_data NVARCHAR(MAX) NULL, 7> binary_data VARBINARY(MAX) NULL 8> ); 9> GO 1> 2> INSERT INTO dbo.CustomerData(custid, txt_data) 3> VALUES(102, "Customer 102 text data"); 4> GO 1> 2> drop table CustomerData; 3> GO</source>
Varchar type value pattern matching
<source lang="sql">
5> create table Bankers( 6> BankerID Integer, 7> BankerName VARCHAR(20), 8> BankerContactLName VARCHAR(20), 9> BankerContactFName VARCHAR(20), 10> BankerCity VARCHAR(20), 11> BankerState VARCHAR(20), 12> BankerZipCode VARCHAR(20), 13> BankerPhone VARCHAR(20) 14> ) 15> 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> select * from Bankers WHERE BankerName LIKE "COMPU_ER%" 3> GO BankerID BankerName BankerContactLName BankerContactFName BankerCity BankerState BankerZipCode BankerPhone
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
(0 rows affected) 1> 2> drop table Bankers; 3> GO</source>