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

• John H Marsh

  SSChampion

  Points: 11671

  More actions

  April 4, 2009 at 1:02 am

  #971984

  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: 1004339

  More actions

  April 4, 2009 at 9:13 am

  #972064

  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

  April 4, 2009 at 10:23 am

  #972076

  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

  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: 1004339

  More actions

  April 4, 2009 at 9:18 pm

  #972125

  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

  April 4, 2009 at 11:17 pm

  #972135

  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

  April 5, 2009 at 1:08 am

  #972142

  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: 1004339

  More actions

  April 5, 2009 at 11:12 am

  #972189

  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

  April 5, 2009 at 2:48 pm

  #972219

  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: 1004339

  More actions

  April 5, 2009 at 7:59 pm

  #972248

  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

  April 6, 2009 at 2:32 am

  #972348

  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

  April 6, 2009 at 8:33 am

  #972570

  Thanks everyone for your inputs...