Removing the NULL, Space, Tab Space, Start of heading,
Horizontal Tab, New Line, Carriage Return, File Separator etc.,. In the SQL
Server.
Actually in the SQL server data base the spaces are looking
like empty string only. For the first time all peoples will think of its space
kind and will use LTRIM () & RTRIM () functions. It will not clear the tabs
space and all. So this type of issue will clear with this post.
This type of the spaces in the SQL server is Tab spaces, NULLs
etc.
Execute the below query to understand yourself on different
type of spaces in the SQL server.
DECLARE @MIN INT=0
DECLARE @MAX INT=255
DECLARE @TEMP AS TABLE (VALUE1 VARCHAR(50),VALUE2 VARCHAR(50))
WHILE @MIN<=@MAX
BEGIN
INSERT INTO @TEMP VALUES(CHAR(@MIN),ASCII(CHAR(@MIN)))
SET @MIN=@MIN+1
END
SELECT * FROM @TEMP
Find the below table to understand the space with other type
of keys in the key board
Sno
|
Char | Result | ASCII | Result2 | Description |
| 0 | Char(0) | ASCII(Char(0)) | 0 | null | |
| 1 | Char(1) | ASCII(Char(1)) | 1 | start of heading | |
| 2 | Char(2) | ASCII(Char(2)) | 2 | start of text | |
| 3 | Char(3) | ASCII(Char(3)) | 3 | end of text | |
| 4 | Char(4) | ASCII(Char(4)) | 4 | end of transmission | |
| 5 | Char(5) | ASCII(Char(5)) | 5 | enquiry | |
| 6 | Char(6) | ASCII(Char(6)) | 6 | acknowledge | |
| 7 | Char(7) | ASCII(Char(7)) | 7 | bell | |
| 8 | Char(8) | ASCII(Char(8)) | 8 | backspace | |
| 9 | Char(9) | ASCII(Char(9)) | 9 | horizontal tab | |
| 10 | Char(10) | ASCII(Char(10)) | 10 | new line | |
| 11 | Char(11) | ASCII(Char(11)) | 11 | vertical tab | |
| 12 | Char(12) | ASCII(Char(12)) | 12 | new page | |
| 13 | Char(13) | ASCII(Char(13)) | 13 | carriage return | |
| 14 | Char(14) | ASCII(Char(14)) | 14 | shift out | |
| 15 | Char(15) | ASCII(Char(15)) | 15 | shift in | |
| 16 | Char(16) | ASCII(Char(16)) | 16 | data link escape | |
| 17 | Char(17) | ASCII(Char(17)) | 17 | device control 1 | |
| 18 | Char(18) | ASCII(Char(18)) | 18 | device control 2 | |
| 19 | Char(19) | ASCII(Char(19)) | 19 | device control 3 | |
| 20 | Char(20) | ASCII(Char(20)) | 20 | device control 4 | |
| 21 | Char(21) | ASCII(Char(21)) | 21 | negative acknowledge | |
| 22 | Char(22) | ASCII(Char(22)) | 22 | synchronous idle | |
| 23 | Char(23) | ASCII(Char(23)) | 23 | end of trans. block | |
| 24 | Char(24) | ASCII(Char(24)) | 24 | cancel | |
| 25 | Char(25) | ASCII(Char(25)) | 25 | end of medium | |
| 26 | Char(26) | ASCII(Char(26)) | 26 | substitute | |
| 27 | Char(27) | ASCII(Char(27)) | 27 | escape | |
| 28 | Char(28) | ASCII(Char(28)) | 28 | file separator | |
| 29 | Char(29) | ASCII(Char(29)) | 29 | group separator | |
| 30 | Char(30) | ASCII(Char(30)) | 30 | record separator | |
| 31 | Char(31) | ASCII(Char(31)) | 31 | unit separator | |
| 32 | Char(32) | ASCII(Char(32)) | 32 | space |
