check spaces

[Maalti Iyer]

Hello All,
How to check blank spaces. In otherwords, how can I check a blank space in a specific byte of a column. I tried using ascii(substr(column_name,start_byte, length) = 32. This does not work if there're blank space starting start_byte all the way up.
Say, for example if the column A has a value = "New York ". ascii(substr(A,4,1) = 32 returns true - but this does not work for ascii(substr(A,9,1) = 32 in my where cluase.
Thanks
Maalti Iyer.

[Mark Spritzler]

This might help partially, but not completely. In your example it looks like you are looking for spaces at the beginning or the end of the data.
The way I find out if there is such records I always use
trim(field) <> field
in the where clause. This will bring out all the records that have an extra space in the beginning or end of the field.
Mark

[Maalti Iyer]

Mark,
It didn't work.
Thanks
maalti

[Mark Spritzler]

I don't understand what didn't work. If you have a where clause that is
trim(field) <> field
then the following would return
field value
'NEW YORK '
since there is a space after the K then
'NEW YORK ' <> 'NEW YORK'
This is 100% accurate in Oracle.
Post your query here so that we can take a look at it.
Mark

[Michael Matola]

where substr( a , 9 , 1 ) = chr( 32 )
where substr( a, 9 , 1 ) = ' '
and your original all worked for me in Oracle.
[ August 08, 2003: Message edited by: Michael Matola ]

[Maalti Iyer]

Mark,
Here's the query
select card_number, trim(card_number) from credit_card where trim(card_number) <> card_number
Actually the column card_number length is 17 and I'm looking for card_number of length 15 (location 16 and 17 spaces).
Thanks
Maalti

[Maalti Iyer]

Guys,
If I look for NULL (instead of spaces) - it works
select card_number, trim(card_number) from credit_card where substr( card_number , 16 , 1 ) is NULL

Thanks
Maalti

[Mark Spritzler]

Ah, so that is waht you were looking for. The other thing you could have tried was
length(trim(card_number)) = 15
Mark

[Praveen Zala]

Hello ....
It works only when the NULL clause is added
Regards
Praveen