CODEWITHSUNDEEP
sqlfirebirdfirebird2.5
Michael Müller
Michael Müller

Reputation: 401

WHERE clause that finds in 123xxx digits

I try to search all rows that contain

123000 - 123xxx

So the last three are unknown but must be filled up.

So I dont want to find for example

12300 or 1230000

Upvotes: 1

Views: 688

Answers (4)

Val Marinov
Val Marinov

Reputation: 2755

For Firebird version 2.1 and above 

select * from table_name
where  
  (BIN_AND(table_name.field_name,123000) = 123000)

in stored procedure with parameter 

select * from table_name
where  
  (BIN_AND(table_name.field_name,:p) = :p)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269933

If the value is a number, then just use:

where value >= 123000 and value < 124000

If value is a string:

where char_length(value) = 6 and left(value, 3) = '123'

Upvotes: 1

jamie evans
jamie evans

Reputation: 1

If your sure the field will always be an int, you could use 

WHERE Field LIKE '123___'

Doc for _ can be found here: here

In short, _ is a single wildcard character, so on a field which can only be an int, this should match 123000 to 123999

This could also be useful if you have to find any reference to 123 where the last 3 values could be anything, including characters

Regards, Jamie

Upvotes: 0

juergen d
juergen d

Reputation: 204766

select * from your_table
where some_column between 123000 and 123999

Upvotes: 3

Related Questions