How to find the one_space string in a CHAR data type?

Question

Post reply

How to find the one_space string in a CHAR data type?

apat

SSCertifiable

Points: 5929
More actions
April 3, 2009 at 3:22 pm

#94752

Hi All,
I have a table with CHAR data type. It has data with empty strings, one space and regular data.
I need to find the records with one_space (NOT empty string).
Here is the DDL.
create table t1 (col char(5))
Go
insert t1 select 'abc'
insert t1 select ''
insert t1 select ' ' -- one space
Apparently I can not use DATALENGTH (which gives 5) or len (which gives 0). Looks like SQL is truncating the cell values before applying these functions.
I can use VARCHAR column data type or some character to replace the empty_string. However the data is already exist in the table. And I need a SELECT on the existing table. Thanks..

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

John H Marsh SSChampion Points: 11671 More actions · Answer 1

Hello,

Is the Column in your Table set as “Not Null“ or “ANSI_Padding On”?

In either of these cases you will have problems because the data will have been padded with trailing spaces when inserted into the column i.e. a Single Space and an Empty String will both be stored identically. I suspect this is the case as you mentioned the DataLength function is returning 5.

To quote BOL “The char data type is a fixed-length data type when the NOT NULL clause is specified. If a value shorter than the length of the column is inserted into a char NOT NULL column, the value is right-padded with blanks to the size of the column. If ANSI_PADDING is ON when a char NULL column is created, it behaves the same as a char NOT NULL column: values are right-padded to the size of the column.”

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

Very good info on the Ansi-Padding and how CHAR columns respond, John...

It's still not going to help for this problem, though, because empty strings (in T-SQL) are never empty in a table. It's one of those things to avoid at design time. In Oracle, empty strings are stored as NULL. Same should be done here.

set ansi_padding OFF

drop table t1

create table t1 (RowNum INT IDENTITY(1,1), col char(5) NULL)

Go

insert t1 select 'abc'

insert t1 select ''

insert t1 select ' ' -- one space

SELECT DATALENGTH(COL),* FROM t1

WHERE Col = ' '

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

apat SSCertifiable Points: 5929 More actions · Answer 3

Thanks for your response... I tried this as below..

use testdb

go

set ansi_padding OFF

--drop table t1

create table t1 (RowNum INT IDENTITY(1,1), col char(5) NULL)

Go

insert t1 select 'abc'

insert t1 select ''

insert t1 select ' ' -- one space

SELECT DATALENGTH(COL) FROM t1

Result

-----------

3

1

So, second row gives 1 as a result whereas there is actually no space between quotes. It should give 0....

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

apat (4/4/2009)
So, second row gives 1 as a result whereas there is actually no space between quotes. It should give 0....

That's what I was saying... you cannot get what you want because you can't actually store an empty string in a table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Boolean_z SSCommitted Points: 1807 More actions · Answer 5

By putting varchar instead of char, it started handling blank and single space properly. But it is not recognizing between single space and more than one space.

set ansi_padding OFF

drop table t1

create table t1 (RowNum INT IDENTITY(1,1), col varchar(5) NULL)

Go

insert t1 select 'abc'

insert t1 select ''

insert t1 select ' ' -- one space

insert t1 select ' ' -- two space

SELECT * FROM t1

WHERE Col = ' ' and DATALENGTH(col)=1

Paul White SSC Guru Points: 150467 More actions · Answer 6

Boolean_z,

Set ANSI_PADDING ON and your life will improve measurably 😉

Books Online
In a future version of SQL Server ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Books Online
We recommend that ANSI_PADDING always be set to ON.

See the entry under "SET ANSI_PADDING (Transact-SQL)"

Jeff Moden (4/4/2009)
That's what I was saying... you cannot get what you want because you can't actually store an empty string in a table.

*ahem*

SET ANSI_PADDING ON;

CREATE TABLE dbo.[50197629-3B44-40C6-9041-E6F579953DD9]

(

row_idINTEGER IDENTITY(1,1) PRIMARY KEY CLUSTERED,

testVARCHAR(55) NOT NULL

);

INSERTdbo.[50197629-3B44-40C6-9041-E6F579953DD9] (test) VALUES ('');

INSERTdbo.[50197629-3B44-40C6-9041-E6F579953DD9] (test) VALUES (CHAR(32));

INSERTdbo.[50197629-3B44-40C6-9041-E6F579953DD9] (test) VALUES (CHAR(32) + CHAR(32));

SELECTrow_id,

DATALENGTH(test) AS [datalength],

LEN(test) AS [len],

REPLACE(test, CHAR(32), 'X') AS [replace],

ASCII(test) AS [ascii]

FROMdbo.[50197629-3B44-40C6-9041-E6F579953DD9];

DROP TABLE dbo.[50197629-3B44-40C6-9041-E6F579953DD9];

-- Spot the difference!

row_id, datalength, len, replace, ascii

100NULL

210X32

320XX32

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

I stand corrected. Thanks for the code.

I'd be curious to know why anyone would want to use the difference between an empty string and a sting of 1 or more spaces as criteria for anything. I'd also be curious why anyone would want to use a fixed length datatype for something with a widely variable length like Microsoft did with the NVARCHAR(128) columns present in the SysProcesses table. Something similar to that caused a log table to contain about 94 gigabytes at work. After removing all of the trailing spaces, the size of that log went down to about 36 gigabytes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 8

Hey Jeff,

I'm with you on the 'why use an empty string or spaces as criteria' thing; although I guess there is some wild and wacky legacy code out there. Unless something is explicitly forbidden by the laws of nature, it'll exist somewhere!

The fixed length columns in sysprocesses is an interesting one. I can only suppose that it was important that all the columns in that table had a fixed offset in the row, maybe for deep dark internal performance reasons...?

As an aside, I find myself very rarely using fixed-length types like CHAR or NCHAR in my tables. The behaviours with and without ANSI_PADDING set are just plain odd to my mind. The sooner all the ANSI standard settings are, er, standardised the better though. The number of times I've not been able to create an indexed view, persist a computed column, or whatever because of odd SETtings....well!

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 9

Heh... ok... understood. And absolutely spot-on with the rest of what you said. Thanks, Paul. And good "meeting" you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 10

Jeff Moden (4/5/2009)
Heh... ok... understood. And absolutely spot-on with the rest of what you said. Thanks, Paul. And good "meeting" you.

Thanks Jeff - and you too! 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

apat SSCertifiable Points: 5929 More actions · Answer 11

apat

SSCertifiable

Points: 5929

April 6, 2009 at 8:33 am

#972570

Thanks everyone for your inputs...