How to tell difference betwwen empty string and one or more blanks

Question

Post reply

How to tell difference betwwen empty string and one or more blanks

sgambale

SSCommitted

Points: 1709
More actions
August 16, 2010 at 1:47 pm

#95001

I have a table defined as:
create table test (a char(3))
If I populate 1 row with the empty string (insert into test values(''),
then another row with one blank,
another with two blanks,
another with three blanks
If I query the table select * from test where a = ''
I get the same 4 rows returned as if I query for one or more blanks.
How can I tell the # of blanks that are in each row?
Say, for example, I want to know the rows that have exactly two blanks are in this column. How do I do it?
Steve

Viewing 14 posts - 1 through 13 (of 13 total)

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

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 1

You don't.

From 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.

Thus all the columns that contain 1 to 3 blanks will all be stored the same way: as 3 blanks.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

i think sql doesn't care about the blanks ( 2 or 3 or 4 space) for both char or varchar.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Lowell SSC Guru Points: 323518 More actions · Answer 3

the LEN() function ignores trailing spaces, even on a CHAR data type, where the DATALENGTH() function respects the spaces;

so to find exactly two trailing spaces in a column:

SELECT * FROM SOMETABLE

WHERE DATALENGTH(SOMECOLUMN) - LEN(SOMECOLUMN) = 2

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sgambale SSCommitted Points: 1709 More actions · Answer 4

Thanks for the replies. It makes sense that 1, 2, or 3 blanks is treated the same (with the entire column filled with blanks), but the empty string is also treated as all blanks in my example. The datalength is 3 and len is 0 for all rows with either the empty string or one or more blanks so I don't see how to separate the empty string from those with at least one blank.

Lowell SSC Guru Points: 323518 More actions · Answer 5

sgambale (8/17/2010)
Thanks for the replies. It makes sense that 1, 2, or 3 blanks is treated the same (with the entire column filled with blanks), but the empty string is also treated as all blanks in my example. The datalength is 3 and len is 0 for all rows with either the empty string or one or more blanks so I don't see how to separate the empty string from those with at least one blank.

if you used VARCHAR instead of CHAR, you would see the difference;

as you've seen, witht he CHAR data type, inserting one empty space is exactly the same as no empty spaces or three empty spaces.

/*

[]00

[ ]10

[ ]20

[ ]30

*/

CREATE TABLE #EXAMPLE(TheText varchar(3) )

INSERT INTO #EXAMPLE

SELECT '' UNION ALL

SELECT ' ' UNION ALL

SELECT ' '

SELECT

'[' + TheText + ']' As TheText,

datalength(TheText) As dLen,

len(TheText) As LLen

From #EXAMPLE

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

sgambale (8/17/2010)
Thanks for the replies. It makes sense that 1, 2, or 3 blanks is treated the same (with the entire column filled with blanks), but the empty string is also treated as all blanks in my example. The datalength is 3 and len is 0 for all rows with either the empty string or one or more blanks so I don't see how to separate the empty string from those with at least one blank.

As Lamprey13 pointed out, you can't, because an empty string and one or more blanks are all stored as the same string of three spaces in a CHAR(3) column. Try this:

DROP TABLE #Test

CREATE TABLE #Test (Char3Column CHAR(3))

INSERT INTO #Test (Char3Column)

SELECT '' UNION ALL SELECT SPACE(1) UNION ALL SELECT SPACE(2) UNION ALL SELECT SPACE(3)

SELECT Char3Column, '['+Char3Column+']'

FROM #Test

-- Note also that UNION ALL is required because UNION will remove the duplicates,

-- resulting in a single row in the sample table.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 7

Lowell (8/17/2010)
sgambale (8/17/2010)
Thanks for the replies. It makes sense that 1, 2, or 3 blanks is treated the same (with the entire column filled with blanks), but the empty string is also treated as all blanks in my example. The datalength is 3 and len is 0 for all rows with either the empty string or one or more blanks so I don't see how to separate the empty string from those with at least one blank.
if you used VARCHAR instead of CHAR, you would see the difference;
as you've seen, witht he CHAR data type, inserting one empty space is exactly the same as no empty spaces or three empty spaces.
/*
[]00
[ ]10
[ ]20
[ ]30
*/
CREATE TABLE #EXAMPLE(TheText varchar(3) )
INSERT INTO #EXAMPLE
SELECT '' UNION ALL
SELECT ' ' UNION ALL
SELECT ' ' UNION ALL
SELECT ' '
SELECT
'[' + TheText + ']' As TheText,
datalength(TheText) As dLen,
len(TheText) As LLen
From #EXAMPLE

datalength is doing here the trick ..thanks for explanation

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

sgambale SSCommitted Points: 1709 More actions · Answer 8

sgambale

SSCommitted

Points: 1709

August 17, 2010 at 6:57 am

#1207848

Good examples with varchar. Thanks.

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

And now, for the question of the day {drum roll please}... why do you need to know how many spaces something has? Why do you need to know the difference between and empty string and a blank string? There are some good reasons and some bad. Tell us what your reason is so we can tell you how to avoid the bad reasons if you have a bad reason.

--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)

sgambale SSCommitted Points: 1709 More actions · Answer 10

I personally did not need to know.

A developer asked me to figure this out based on some user issues they had. He did not elaborate.

I don't think distinguishing between one vs two vs three blanks matters: it's still blanks.

Knowing you have an empty string is a bit different than blanks, although I couldn't give a strong case to support that.

Steve

sgambale SSCommitted Points: 1709 More actions · Answer 11

A developer asked me about this. He did not elaborate except to say there were user issues involved.

I don't think anyone should need to distinguish between one vs two vs three blanks: blanks are blanks.

Knowing between an empty string vs. blanks might be of some value, although I couldn't give you a great reason.

Sometimes the empty string could mean no value, but you don't want to allow nulls, so you use the empty string.

Maybe you're defaulting the column to the empty string and anything else means the value was changed, even if it changed to blanks.

LerxtDBA SSCrazy Points: 2190 More actions · Answer 12

This may be a case where it's worth asking the developer why they need the information their asking for or what they plan to do with it. There are times when that insight will help you see the real goal and be able to provide them with something more appropriate. Sometimes they're asking for an intermediate step when you can actually just give them the final answer.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 13

sgambale (8/17/2010)
A developer asked me about this. He did not elaborate except to say there were user issues involved.
I don't think anyone should need to distinguish between one vs two vs three blanks: blanks are blanks.
Knowing between an empty string vs. blanks might be of some value, although I couldn't give you a great reason.
Sometimes the empty string could mean no value, but you don't want to allow nulls, so you use the empty string.
Maybe you're defaulting the column to the empty string and anything else means the value was changed, even if it changed to blanks.

Very well could be that there were "user issues" involved where you do actually need to know the difference (which is why I was really asking). For example...

--===== Pretend this is a new table built to save space

CREATE TABLE dbo.NewTable (SomeID INT, SomeVarChar VARCHAR(100))

--===== Pretend this is the old space-wasting table (We insert a row with an "empty" string)

CREATE TABLE dbo.OldTable (SomeID INT, SomeChar CHAR(100))

INSERT INTO dbo.OldTable

(SomeID, SomeChar)

SELECT 1,''

--===== Ok... copy the data from the old table to the new table to "save space".

INSERT INTO dbo.NewTable

(SomeID, SomeVarChar)

SELECT SomeID, SomeChar

FROM dbo.OldTable

--===== Did we actually save any space? Most folks thought so because it

-- was a VARCHAR column. It drops trailing spaces, right?

SELECT LEN(SomeVarChar)

FROM dbo.NewTable

--===== Heh... obviously, that's just not true. ;-)

SELECT DATALENGTH(SomeVarChar)

FROM dbo.NewTable

When I got done with that 50GB table, it was only about 5.5GB. As someone famous once said, "It's what you [font="Arial Black"]don't [/font]see that will kill 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)