Trailing Blanks in Comparisons !

[BudiBambang]

Hi,

I have database with trailling blanks in comparisons.

How to make my database ignore trailling blanks in comparisons.

Thanks

 

[JeanNiBee]

Use the various 'trim' functions like "ltrim() and rtrim()"



-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[BudiBambang]

Yes, we can use trim function,
but reading or select with trim function make system slowly.
NB:
We have try two statement :

1) Select * from master where kode=xx
2) select * from master where trim(kode)=xx

Statement number two take a long time depend statement nomber one

 

[JeanNiBee]

If you ned to ignore the trailing blanks you can use varchar instead of char.

varchar strips the trailing spaces automatically.

There is no way to make it ignore the spaces via a configuration setting.

Your query should not be "THAT" much longer when it runs as trim is run inline so it doesn't have much overhead. You may have a different issue with your database.




-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[VDavid]

BudiBambang,

Maybe you have solved already, but here is a thought.

I'm not sure in ASE but in ASA when you create the database file trought Sybase Central, there is an option to 'Ignored trailing blanks', and (I'm just guessing) there should be a way to change that option in a already create database. I've read about a traceflag (T4048) for ignore trailing blanks when selecting but, i'm not sure.

As for,

1) Select * from master where kode=xx
2) select * from master where trim(kode)=xx

Yes, you're right i think the second statement should take longer. If KODE have and index, when you use trim(kode) you're no longer using that index, bacause kode is different from trim(kode).
This is what i heard it happends, if there is a guru that can enlighten us here, we'll appreciated it.

Hope this helps,
David.

 

[BudiBambang]

VDavid,

Yes I was created my Database using ASA Sybase Central but unfortunately I forgot to flag 'Ignored trailing blanks'

and now my database very big, I want to flag 'Ignored trailing blanks' in my old database, but I don't know how to do it?

Anybody know? Help ?

Thanks

 

[VDavid]

Well, I made a little research and seems like there is not way to change the 'Ignore trailing blank' option in a already created database. You'll have to recreate it. That means
- unload the database (data and structure)
- create a new one with the options you want
- reload the database

Hope this helps,

David.

 

[coders4hire]

You can't do an update query?

UPDATE table SET field = trim(field)

---
doug@coders4hire.com

http://www.coders4hire.com

http://www.payraisecalculator.com

http://www.cliintel.com