CODEWITHSUNDEEP
sqlselectconditional-statements
biji buji
biji buji

Reputation: 465

SQL Select by condition on a integer field

I have an integer column in my table. It is product id and has values like 

112233001  
112233002  
113311001  
225577001

This numbering (AABBCCDDD) is formed of 4 parts: 

AA : first level category  
BB : second level category  
CC : third level category  
DDD : counter

I want to check condition in my SELECT statement to select rows that for example have BB = 33 and AA = 11

Please help

Upvotes: 0

Views: 7784

Answers (5)

shareef
shareef

Reputation: 9581

SELECT * FROM YOURTABLE

WHERE 

substr(PRODUCT_ID, 3, 2)='33'
AND
substr(PRODUCT_ID, 1, 2)='11'

OR

SELECT * FROM YOURTABLE

WHERE 

PRODUCT_ID LIKE '11%33%'

and yes in short you have to convert to string

reference of substr

Purpose

The SUBSTR functions return a portion of char, beginning at character position, substring_length characters long. SUBSTR calculates lengths using characters as defined by the input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses Unicode complete characters. SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points.

If position is 0, then it is treated as 1.

If position is positive, then Oracle Database counts from the beginning of char to find the first character.

If position is negative, then Oracle counts backward from the end of char.

If substring_length is omitted, then Oracle returns all characters to the end of char. If substring_length is less than 1, then Oracle returns null.

char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. Both position and substring_length must be of datatype NUMBER, or any datatype that can be implicitly converted to NUMBER, and must resolve to an integer. The return value is the same datatype as char. Floating-point numbers passed as arguments to SUBSTR are automatically converted to integers.

Upvotes: 1

Kshitij
Kshitij

Reputation: 8614

u need to use _ wildcard char -

SELECT * 
FROM TABLE
WHERE
FIELD LIKE '1133_____'

here, each _ is for one char. So you need to put the same number of _ to keep the length same

Upvotes: 0

John Kane
John Kane

Reputation: 4443

This seems like it could work. Otherwise you may have to cast them as strings and parse the values out that you need which would make your queries much slower.

SELECT * 
    FROM table t
WHERE t.field >= 113300000
AND t.field < 113400000

Upvotes: 0

Paddy
Paddy

Reputation: 33857

Would this suffice:

select x from table where field >= 113300000 and field < 113400000

Upvotes: 4

indiPy
indiPy

Reputation: 8062

          Select field from table where substr(field,,) = value

Upvotes: 1

Related Questions