SQL Server/T-SQL Tutorial/String Functions/Char function

Материал из SQL эксперт
Версия от 13:22, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

A SELECT statement that uses the CHAR function to format output

   <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> 2> 3> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 4> GO (1 rows affected) 1> 2> 3> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 4> GO (1 rows affected) 1> 2> 3> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 4> GO (1 rows affected) 1> 2> 3> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 4> GO (1 rows affected) 1> 2> 3> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 4> GO (1 rows affected) 1> 2> 3> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 4> GO (1 rows affected) 1> 2> 3> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 4> GO (1 rows affected) 1> 2> 3> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 4> GO (1 rows affected) 1> 2> 3> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 4> GO (1 rows affected) 1> 2> 3> 4> 5> SELECT BankerName + CHAR(13) + CHAR(10) 6> + BankerCity + ", " + BankerState + " " + BankerZipCode 7> FROM Bankers 8> WHERE BankerID = 1 9> GO


ABC Inc. Vancouver, BC 11111 (1 rows affected) 1> 2> 3> drop table Bankers; 4> GO</source>


CHAR(13)

   <source lang="sql">

3> CREATE TABLE authors( 4> au_id varchar(11), 5> au_lname varchar(40) NOT NULL, 6> au_fname varchar(20) NOT NULL, 7> phone char(12) NOT NULL DEFAULT ("UNKNOWN"), 8> address varchar(40) NULL, 9> city varchar(20) NULL, 10> state char(2) NULL, 11> zip char(5) NULL, 12> contract bit NOT NULL 13> ) 14> GO 1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1) 2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1) 3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1) 4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1) 5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1) 6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1) 7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1) 8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0) 9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0) 10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1) 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 rows affected) (1 rows affected) 1> 2> 3> SELECT "NAME"=au_fname + CHAR(13) + au_lname 4> FROM authors 5> GO NAME


Joe Jack Pink Blue Red Black White Yellow Gold Siler (10 rows affected) 1> 2> drop table authors; 3> GO 1> 2></source>


CHAR(ASCIIValue)

   <source lang="sql">

2> -- Create temporary table for numbers: 3> Create Table #ASCIIVals (ASCIIValue SmallInt) 4> GO-- Insert numbers 0 - 127 into table: 1> Insert Into #ASCIIVals (ASCIIValue) Select 0 2> Insert Into #ASCIIVals (ASCIIValue) Select 1 3> Insert Into #ASCIIVals (ASCIIValue) Select 2 4> Insert Into #ASCIIVals (ASCIIValue) Select 3 5> Insert Into #ASCIIVals (ASCIIValue) Select 4 6> Insert Into #ASCIIVals (ASCIIValue) Select 123 7> Insert Into #ASCIIVals (ASCIIValue) Select 124 8> Insert Into #ASCIIVals (ASCIIValue) Select 125 9> Insert Into #ASCIIVals (ASCIIValue) Select 126 10> Insert Into #ASCIIVals (ASCIIValue) Select 127 11> GO-- Return all integer values and corresponding ASCII characters: (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 rows affected) (1 rows affected) 1> SELECT ASCIIValue, CHAR(ASCIIValue) As Character FROM #ASCIIVals 2> 3> drop table #ASCIIVals; 4> GO ASCIIValue Character


---------
        0
        1 ?
        2 ?
        3 ?
        4 ?
      123 {
      124 |
      125 }
      126 ~
      127 ?

(10 rows affected)</source>


CHAR is the reverse of the ASCII function as it changes a numeric value in to an ASCII character.

   <source lang="sql">

4> DECLARE @ASCII_VALUE INT 5> SET @ASCII_VALUE = 82 6> SELECT CHAR(@ASCII_VALUE) 7> GO - R (1 rows affected)</source>