SQL to find if field is low values

Pattio · #1 (**permalink**) 05-30-07, 12:12

I need assistance with a field in the first five positions is low-values and then has either spaces or values -- I need to get the ones where only the first 5 positions are low values?

gvee · #2 (**permalink**) 05-30-07, 12:40

Fancy posting some example data and what your expect your results to look like?

Pattio · #3 (**permalink**) 05-30-07, 12:53

Here is an example of the data in column addr1 pic x(20):

123 Cannon Way
X'0000000000' Nasco place
X'0000000000' Yellow Rd
456 Jamison Ave

In my select I want to get the 2 & 3 data example in my select, when i put the following SQL

Select addr1
,addr2
From TWIP120
Where addr1 = X'00'

I don't get rows 2 & 3 back because my whole data field isn't low values

gvee · #4 (**permalink**) 05-30-07, 13:35

I'm lost in translation...

Code:

Where addr1 = X'00'

This isn't valid SQL.
And what do you mean by "low values"

Pat Phelan · #5 (**permalink**) 06-02-07, 22:01

Quote:

Originally Posted by Pattio

I need assistance with a field in the first five positions is low-values and then has either spaces or values -- I need to get the ones where only the first 5 positions are low values?

The syntax varies depending on the SQL dialect... What vendor (DB2, Oracle, Microsoft, MySQL, etc) and version (1.2, 10.5, Rockafeller, Tornado, etc) of SQL are you using?

-PatP

Peter.Vanroose · #6 (**permalink**) 06-10-07, 16:43

Quote:

Originally Posted by Pattio

Select ...
From TWIP120
Where addr1 = X'00'

I don't get rows 2 & 3 back because my whole data field isn't low alues

What about

Code:

Select ...
     From TWIP120 
   Where substr(addr1, 1, 5) = X'00'

gvee · #7 (**permalink**) 06-11-07, 03:42

Code:

Select ...
     From TWIP120 
   Where substr(addr1, 1, 5) = X'00'

Won't you hit syntax errors with the single quotes?

r937 · #8 (**permalink**) 06-11-07, 05:05

SUBSTR isn't standard sql either

gvee · #9 (**permalink**) 06-11-07, 05:27

Change to a LIKE comparison?

Peter.Vanroose · #10 (**permalink**) 06-11-07, 15:04

Still a bit platform-specific, but instead of asking for 'equal to "LOW VALUES"' one could maybe ask for 'smaller than any normal text'.
When the underlying system uses ASCII or UNICODE, and assuming no ASCII values between 1 and 31 are present as first character of the addr1 field, the following would give the wanted rows:

Code:

Select ...
     From TWIP120 
   Where addr1 < ' '

Internet Services

Web Development

Digital Marketing

Consumer Tech