• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Junilu Lacar
  • Liutauras Vilda
Sheriffs:
  • Paul Clapham
  • Jeanne Boyarsky
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Piet Souris
  • Carey Brown
Bartenders:
  • Jesse Duncan
  • Frits Walraven
  • Mikalai Zaikin

check spaces

 
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
ranger
Posts: 17346
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Mark,
It didn't work.
Thanks
maalti
 
Mark Spritzler
ranger
Posts: 17346
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 52
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
ranger
Posts: 17346
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ah, so that is waht you were looking for. The other thing you could have tried was
length(trim(card_number)) = 15
Mark
 
Ranch Hand
Posts: 118
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello ....
It works only when the NULL clause is added
Regards
Praveen
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic