use pubs
SET QUOTED_IDENTIFIER OFF
SET ANSI_DEFAULTS OFF
go
SET ANSI_PADDING OFF -- OFF IS THE DEFAULT IF NOTHING IS SET
go
IF EXISTS (SELECT * FROM sysobjects WHERE name='CHECKPAD' and type='U')
DROP TABLE CHECKPAD
go
CREATE TABLE CHECKPAD
(
ROWID SMALLINT NOT NULL PRIMARY KEY,
C10NOT CHAR(10) NOT NULL,
C10NUL CHAR(10) NULL,
V10NOT VARCHAR(10) NOT NULL,
V10NUL VARCHAR(10) NULL
)
go
select name, 'Paddings On'=CASE WHEN status & 20 <> 0 THEN 'YES' ELSE 'NO' END
from syscolumns where id=object_id('CHECKPAD') and name='V10NOT'
go
-- Row 1 has names with no trailing blanks
INSERT CHECKPAD VALUES (1,'John','John','John','John')
-- Row 2 has each name inserted with 3 trailing blanks.
INSERT CHECKPAD VALUES (2,'John ','John ','John ','John ')
-- Row 3 has each name inserted with a full 6 trailing blanks.
INSERT CHECKPAD VALUES (3,'John ','John ','John ','John ')
-- Row 4 has each name inserted with too many - 7 trailing blanks.
INSERT CHECKPAD VALUES (4,'John ','John ','John ','John ')
SELECT ROWID,
"0xC10NOT"=convert(varbinary(10),C10NOT),L1=datalength(C10NOT),
"LIKE 'John %'"=CASE WHEN (C10NOT LIKE 'John %') THEN 1 ELSE 0 END,
"LIKE 'John %'"=CASE WHEN (C10NOT LIKE 'John %') THEN 1 ELSE 0 END,
"0xC10NUL"=convert(varbinary(10),C10NUL),L2=datalength(C10NUL),
"LIKE 'John %'"=CASE WHEN (C10NUL LIKE 'John %') THEN 1 ELSE 0 END,
"LIKE 'John %'"=CASE WHEN (C10NUL LIKE 'John %') THEN 1 ELSE 0 END,
"0xV10NOT"=convert(varbinary(10),V10NOT),L3=datalength(V10NOT),
"LIKE 'John %'"=CASE WHEN (V10NOT LIKE 'John %') THEN 1 ELSE 0 END,
"LIKE 'John %'"=CASE WHEN (V10NOT LIKE 'John %') THEN 1 ELSE 0 END,
"0xV10NUL"=convert(varbinary(10),V10NUL),L4=datalength(V10NUL),
"LIKE 'John %'"=CASE WHEN (V10NUL LIKE 'John %') THEN 1 ELSE 0 END,
"LIKE 'John %'"=CASE WHEN (V10NUL LIKE 'John %') THEN 1 ELSE 0 END
FROM CHECKPAD
go
SET ANSI_PADDING ON
go
DROP TABLE CHECKPAD
go
CREATE TABLE CHECKPAD
(
ROWID SMALLINT NOT NULL PRIMARY KEY,
C10NOT CHAR(10) NOT NULL,
C10NUL CHAR(10) NULL,
V10NOT VARCHAR(10) NOT NULL,
V10NUL VARCHAR(10) NULL
)
go
select name, 'Paddings On'=CASE WHEN status & 20 <> 0 THEN 'YES' ELSE 'NO' END
from syscolumns where id=object_id('CHECKPAD') and name='V10NOT'
go
-- Row 1 has names with no trailing blanks
INSERT CHECKPAD VALUES (1,'John','John','John','John')
-- Row 2 has each name inserted with 3 trailing blanks.
INSERT CHECKPAD VALUES (2,'John ','John ','John ','John ')
-- Row 3 has each name inserted with a full 6 trailing blanks.
INSERT CHECKPAD VALUES (3,'John ','John ','John ','John ')
-- Row 4 has each name inserted with too many - 7 trailing blanks.
INSERT CHECKPAD VALUES (4,'John ','John ','John ','John ')
SELECT ROWID,
"0xC10NOT"=convert(varbinary(10),C10NOT),L1=datalength(C10NOT),
"LIKE 'John %'"=CASE WHEN (C10NOT LIKE 'John %') THEN 1 ELSE 0 END,
"LIKE 'John %'"=CASE WHEN (C10NOT LIKE 'John %') THEN 1 ELSE 0 END,
"0xC10NUL"=convert(varbinary(10),C10NUL),L2=datalength(C10NUL),
"LIKE 'John %'"=CASE WHEN (C10NUL LIKE 'John %') THEN 1 ELSE 0 END,
"LIKE 'John %'"=CASE WHEN (C10NUL LIKE 'John %') THEN 1 ELSE 0 END,
"0xV10NOT"=convert(varbinary(10),V10NOT),L3=datalength(V10NOT),
"LIKE 'John %'"=CASE WHEN (V10NOT LIKE 'John %') THEN 1 ELSE 0 END,
"LIKE 'John %'"=CASE WHEN (V10NOT LIKE 'John %') THEN 1 ELSE 0 END,
"0xV10NUL"=convert(varbinary(10),V10NUL),L4=datalength(V10NUL),
"LIKE 'John %'"=CASE WHEN (V10NUL LIKE 'John %') THEN 1 ELSE 0 END,
"LIKE 'John %'"=CASE WHEN (V10NUL LIKE 'John %') THEN 1 ELSE 0 END
FROM CHECKPAD
go
SET ANSI_PADDING OFF -- OFF IS THE DEFAULT IF NOTHING IS SET
go
