팁 같지도 않은 팁을 올려서 죄송 ㅜ.ㅜ

공백 문자로 인하여 다양하게 문제가 일어나네요... 이번참에 개념 정리....

 

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><?xml:namespace prefix = o /> 

/*

제 목: 끝 공백문자들을SQL에서는어떻게처리하는걸까??

작성자: 주디아줌마

작성일: 2009.05.21 ~ 2009.05.22

*/

 

SQL에서는열이정의된열크기보다짧은값을저장하는방법과char, varchar, binary varbinary

데이터에후행공백이있는값을저장하는방법을제어합니다.

 

구문>>

SET ANSI_PADDING { ON | OFF }

 

link>>

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/tsqlref9/html/92bd29a3-9beb-410e-b7e0-7bc1dc1ae6d0.htm

 

SET ANSI_PADDING ON일때SQL에서는후행의공백에대해서는잘리지않고저장한다.

  

보이지않은공백문자spacebar(0x20), tab, enter ,, spacebar 공백처리에대해서만padding 처리하는 것으로보인다. , 인덱스생성, 집계, 검색에 끝 공백문자들에 대해서는padding 처리한다.

 

결국에는 공백 문자가 보고 싶지 않다면 RTRIM() 함수를이용하여 저장해야 한다.

후 행의 공백문자를 제거한다.

 

 

---------------------------------------

-- CASE 1

-- sapcebartab의문자코드값은멀까?

---------------------------------------

IF OBJECT_ID('T') IS NOT NULL

BEGIN

             DROP TABLE T

END

GO

 

CREATE TABLE dbo.T

(

             Seq                    INT IDENTITY(1,1) NOT NULL,

             strID      VARCHAR(100)

            

             -- CONSTRAINT UCL_T_strID PRIMARY KEY (strID)

)

GO

 

INSERT INTO dbo.T (strID) VALUES(' AAA')

INSERT INTO dbo.T (strID) VALUES('AAA')

INSERT INTO dbo.T (strID) VALUES('AAA ') -- space bar      1 count

INSERT INTO dbo.T (strID) VALUES('AAA     ')        -- space bar      2 count

INSERT INTO dbo.T (strID) VALUES('AAA          ')              -- space bar      3 count

INSERT INTO dbo.T (strID) VALUES('AAA    ')           -- tab  1

INSERT INTO dbo.T (strID) VALUES('AAA                  ')           -- tab  2

INSERT INTO dbo.T (strID) VALUES('AAA   

')           -- Enter Key 1

INSERT INTO dbo.T (strID) VALUES('AAA   

 

 

')           -- Enter Key 2

INSERT INTO dbo.T (strID) VALUES('BBB')

INSERT INTO dbo.T (strID) VALUES('CCC')

GO

 

SELECT CONVERT(VARBINARY, strID), *

FROM DBO.T

ORDER BY strID ASC

GO

 

----- spacebar 20, tab 09 이구만..ㅋㅋ

--(No column name)       Seq       strID

--0x20414141      1            AAA

--0x414141         2            AAA

--0x41414120      3            AAA

--0x4141412020202020     4            AAA    

--0x41414120202020202020202020 5            AAA         

--0x41414120090D0A        8            AAA   

--0x41414120090D0A0D0A0D0A     9            AAA       

--0x41414109      6            AAA

--0x4141410909  7            AAA 

--0x424242         10          BBB

--0x434343         11          CCC

 

 

SELECT CONVERT(VARBINARY, strID), *

FROM DBO.T

WHERE strID = 'AAA'

ORDER BY strID ASC

GO

 

----- 어쭈이것봐라~~~~ 같은문자로보네,,,

----- 앞의공백은다른문자로보냉.. 어쭈구리.

--(No column name)       Seq       strID

--0x414141         2            AAA

--0x41414120      3            AAA

--0x4141412020202020     4            AAA    

--0x41414120202020202020202020 5            AAA     

   

-- 어디보자집계처리는?? group by ??

SELECT CONVERT(VARBINARY, strID), strID

FROM DBO.T

GROUP BY strID

ORDER BY strID ASC

GO

 

---- 어쭈,, 이거뭐냐??????? 공백은하나로생각하는구나, spacebar가한개~N개는모두하나로

---- 인식하는구낭... 이런뭥미..spacebar, tab 처리는spacebar 처리기법이다르구만...

---- 또한, 앞의공백은다르다.

--(No column name)       strID

--0x20414141      AAA

--0x414141         AAA

--0x41414120090D0A        AAA   

--0x41414120090D0A0D0A0D0A     AAA       

--0x41414109      AAA

--0x4141410909  AAA 

--0x424242         BBB

--0x434343         CCC

 

---------------------------------------

-- CASE 2

-- 유니크한인덱스를만들어서,,,, spacebar 1개일때와2개일때와의차이체크해보자

---------------------------------------

 

IF OBJECT_ID('T_IDX') IS NOT NULL

BEGIN

             DROP TABLE T_IDX

END

GO

CREATE TABLE dbo.T_IDX

(

             Seq                    INT IDENTITY(1,1) NOT NULL,

             strID      VARCHAR(20)

            

             CONSTRAINT UCL_T_IDX_strID PRIMARY KEY (strID)

)

GO

 

INSERT INTO dbo.T_IDX (strID) VALUES(' AAA')

INSERT INTO dbo.T_IDX (strID) VALUES('AAA')

INSERT INTO dbo.T_IDX (strID) VALUES('AAA ')        -- space bar      1 count

INSERT INTO dbo.T_IDX (strID) VALUES('AAA     ') -- space bar      2 count

INSERT INTO dbo.T_IDX (strID) VALUES('AAA          ')       -- space bar      3 count

INSERT INTO dbo.T_IDX (strID) VALUES('AAA           ')           -- tab  1

INSERT INTO dbo.T_IDX (strID) VALUES('AAA                        ')           -- tab  2

INSERT INTO dbo.T_IDX (strID) VALUES('AAA         

')           -- Enter Key 1

INSERT INTO dbo.T_IDX (strID) VALUES('AAA         

 

 

')           -- Enter Key 2

INSERT INTO dbo.T_IDX (strID) VALUES('BBB')

INSERT INTO dbo.T_IDX (strID) VALUES('CCC')

GO

 

-- spacebar에대한공백처리에대해서는INDEX 처리시오류가생기는군...

-- 문자코드값으로전혀다른데, 어찌,,,, 동일하게보는것일까?

-- 위의처리부분에서보면,, 쿼리결과값은,,,

-- 역시동일하구낭...

SELECT CONVERT(VARBINARY, strID), *

FROM DBO.T_IDX

ORDER BY strID ASC

GO

 

---- 어쭈구리..... 등록이안되잖아?????? 이런줴길,,

---- 처리를어찌한다는거지? SQL에서,,,

--(No column name)       Seq       strID

--0x20414141      1            AAA

--0x414141         2            AAA

--0x41414120090D0A        8            AAA   

--0x41414120090D0A0D0A0D0A     9            AAA       

--0x41414109      6            AAA

--0x4141410909  7            AAA 

--0x424242         10          BBB

--0x434343         11          CCC

 

-- LIKE 검색은어찌되는게냐?

SELECT CONVERT(VARBINARY, strID), *

FROM DBO.T_IDX

WHERE strID LIKE 'AAA%'

GO

 

--(No column name)       Seq       strID

--0x414141         2            AAA

--0x41414120090D0A        8            AAA   

--0x41414120090D0A0D0A0D0A     9            AAA       

--0x41414109      6            AAA

--0x4141410909  7            AAA 

 

-- 공백이포함된문자열은어찌검색되느냐?

SELECT CONVERT(VARBINARY, strID), *

FROM DBO.T_IDX

WHERE strID LIKE 'AAA %'

GO

 

--(No column name)       Seq       strID

--0x41414120090D0A        8            AAA   

--0x41414120090D0A0D0A0D0A     9            AAA