CODEWITHSUNDEEP
sqloracle-databaseselectwhitespace
jprockbelly
jprockbelly

Reputation: 1607

SQL select from list where white space has been added to end

I'm trying to select some rows from an Oracle database like so:

select * from water_level where bore_id in ('85570', '112205','6011','SP068253');

This used to work fine but a recent update has meant that bore_id in water_level has had a bunch of whitespace added to the end for each row. So instead of '6011' it is now '6011 '. The number of space characters added to the end varies from 5 to 11.

Is there a way to edit my query to capture the bore_id in my list, taking account that trialling whitespace should be ignored?

I tried:

select * from water_level where bore_id in ('85570%', '112205%','6011%','SP068253%');

which returns more rows than I want, and

select * from water_level where bore_id in ('85570\s*', '112205\s*','6011\s*', 'SP068253\s*');

which didn't return anything?

Thanks JP

Upvotes: 0

Views: 1523

Answers (3)

pilcrow
pilcrow

Reputation: 58589

I'm guessing bore_id is VARCHAR or VARCHAR2. If it were CHAR, Oracle would use (SQL-standard) blank-padded comparison semantics, which regards 'foo' and 'foo ' as equivalent.

So, another approach is to force comparison as CHARs:

SELECT *
  FROM water_level
 WHERE CAST(bore_id AS CHAR(16)) IN ('85570', '112205', '6011', 'SP068253');

Upvotes: 0

Tom
Tom

Reputation: 2230

You should RTRIM the WHERE clause

select * from water_level where RTRIM(bore_id) in ('85570', '112205','6011');

To add to that, RTRIM has an overload which you can pass a second parameter of what to trim, so if the trailing characters weren't spaces, you could remove them. For example if the data looked like 85570xxx, you could use:

select * from water_level where RTRIM(bore_id, 'x') IN ('85570','112205', '6011');

Upvotes: 1

Jimmie R. Houts
Jimmie R. Houts

Reputation: 7818

You could use the replace function to remove the spaces

select * from water_level where replace(bore_id, ' ', '') in ('85570', '112205', '6011', 'SP068253');

Although, a better option would be to remove the spaces from the data if they are not supposed to be there or create a view.

Upvotes: 1

Related Questions