How to find the one_space string in a CHAR data ty

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

How to find the one_space string in a CHAR data ty

Author

Topic

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2009-04-03 : 16:55:31

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

------------------------
I think, therefore I am - Rene Descartes

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-03 : 17:20:05

Char NULL column would pad with trailing spaces to the length of the column when ANSI_PADDING is ON. Thats what is happening in your case.
LEN function would always trim spaces for calculating length.

In your case, I don't think its possible finding which record is just one space since you inserted with ANSI_PADDING ON to a char NULL column, Hence all records have been stored with trailing spaces to the length of column.

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2009-04-03 : 17:33:52

Thanks sakets_2000.

Even ANSI_PADDING OFF is not working for me. I tried the folloiwng code.

SET ANSI_PADDING OFF

create table t1 (col char(5))
Go

insert t1 select 'abc'
insert t1 select ''-- empty string
insert t1 select ' ' -- one space
go

select *, len(col) from t1

------------------------
I think, therefore I am - Rene Descartes

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-03 : 17:39:46

that won't work either. varchar datatype with ANSI_PADDING ON will work though. Also, Len always trims trailing spaces, so you'd want to use datalength.

Try this,

create table t1 (col varchar(500))

Go 
SET ANSI_PADDING ON
GO
insert into t1(col) select 'abc'
insert into t1 select '' 
insert into t1 select ' ' -- one space

select datalength(col) from t1

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2009-04-06 : 11:09:18

Thank you, sakets_2000.

------------------------
I think, therefore I am - Rene Descartes

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-06 : 11:36:47

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources