Handling white space in sql 2005

Question

Handling white space in sql 2005

sbalaven

Ten Centuries

Points: 1127
More actions
July 9, 2008 at 3:44 am

#187936

Hi,
How to handle white space in sql 2005. Is there any specific collation setting available for handling white space
Regards,
S.Balavenkatesh

Viewing 15 posts - 1 through 15 (of 18 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

You have to be more specific. White space is a generic term that includes spaces, carriage returns, line feeds, NULL, etc. In many cases, you need to retain these characters, in some cases you want to remove them. You have data types that force you to have it (CHAR()) and some that do not (VARCHAR()).

Do you have a specific place you need to remove some of these characters?

sbalaven Ten Centuries Points: 1127 More actions · Answer 2

We need to store Whitespace in Primary Key as in oracle . For Eg "Word" and "Word " should be stored in the primary key column without any problem.

Also I need to have "Axa" and "AxA" treated as two separate objects

Hope this clarify you.

How to solve this issues.

Regards,

S.Balavenkatesh

sbalaven Ten Centuries Points: 1127 More actions · Answer 3

Also I need to have "Axa" and "AxA" treated as two separate rows in the primary key

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

Not that I would advocate what you are doing - looks like a real nightmare to me, but you just need to use a case-sensitive collation for the column. Look them up in bools online.

sbalaven Ten Centuries Points: 1127 More actions · Answer 5

Hi

Now i use SQL_Latin1_General_CP1_CI_AS collation settings.

It works for white space before the values. (Eg.' Bala')

It shows error on when i insert like this ('Bala ')

The name column is the primary key when i insert the following values ('Bala'), ('BalA') it shows primary key violation error.

Is there any specific setting used to solve this issues.

Regards,

S.Balavenktesh

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 6

The "CI" indicates case-insensitive. "A" and "a" are the same. If you want the same collation but case-sensitive, it is: SQL_Latin1_General_Cp1_CS_AS

I suggest you look carefully at the collation options and make sure you understand the implications of their use.

sbalaven Ten Centuries Points: 1127 More actions · Answer 7

Hi,

Thanks for your help. It works for ('Bala','BalA')

But it is failed on tailing white space on the values ('Bala ')

Regards,

S.balavenkatesh

kevin.kunz SSChasing Mays Points: 635 More actions · Answer 8

In saying that it fails for "Bala " are you saying that you want that recognized as a seperate item?

sbalaven Ten Centuries Points: 1127 More actions · Answer 9

sbalaven

Ten Centuries

Points: 1127

July 9, 2008 at 7:27 am

#840114

Yes. It should be separate item

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 10

That actually violates the ANSI standard - look at the collation options, but I am not sure if you can get that to actually work.

sbalaven Ten Centuries Points: 1127 More actions · Answer 11

I'm working in one migration from Oracle to SQL server and the Oracle has this feature and teh customer wants in SQL Server Also.

Any solution to this.

Jeff Moden SSC Guru Points: 1003943 More actions · Answer 12

While COLLATE will take care of case sensitivity and the like, trailing white space is a huge PITA... I think anyone that depends on trailing white space is asking for trouble. 😉

That not withstanding, here's one way to do it...

DECLARE @String1 VARCHAR(20),

@String2 VARCHAR(20)

SELECT @String1 = 'Bala',

@String2 = 'Bala '

SELECT CASE

WHEN @String1 = @String2

AND DATALENGTH(@String1) = DATALENGTH(@String2)

THEN 'Match'

ELSE 'No Match'

END

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

sbalaven Ten Centuries Points: 1127 More actions · Answer 13

Thanks Jeff.

But i need to store them as well in the primary key column.

Thanks and Regards

Bala

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 14

Right, so following Jeff's plan, a solution for your PK on the table would be to add an additional field that holds the length of the data in your column and make this part of a surrogate key. Another option, I suppose would be to add an additional character to the end of every item so your trailing space is actually container in the data (make "Joe " into "Joe ^" and "Joe" into "Joe^" and then strip off the extra character in the application.

Either way, you are really asking for trouble making keys that depend on trailing spaces. I would recommend you go back to your clients and try to convince them of the folly of their ways.