Query that looks for an even or odd number in a certain position 1

[shagymoe]

Hi,

I am trying to figure out how to do a select on a group of part numbers with the following format.

6JH02XDVAD
6JH23XDVAD

I need to find the parts where the fifth character is ODD or EVEN.

The fourth character is random.

I can get all the parts with this select:

SELECT * FROM PARTS WHERE NAME LIKE '6JH%'

Any help would be greatly appreciated. Thank you.

 

[grega]

What DB are you using? This would probably work for Sybase/MS SQL Server ...

select * from parts where convert(int,substring(name,5,1)) in (1,3,5,7,9)

Thats for odd numbers, so just change the in list to even numbers.

It's a bit messy but if I think of something else I'll post back.

Greg.

 

[shagymoe]

Sorry, I should have mentioned that I am using Oracle.

 

[karluk]

The Oracle syntax isn't that much different than SQL Server.

select * from parts where substr(name,5,1) in ('1','3','5','7','9');

You don't really need to convert the fifth character to a number but, if you want to do so, use the to_number function.

select * from parts where to_number(substr(name,5,1)) in (1,3,5,7,9);

 

[shagymoe]

I messed up the syntax. It is actually its own column IN a table. So the select looks like this.

SELECT NAME FROM PARTS WHERE PART_NUMBER LIKE '6JH%[ODD OR SOMTHING]';

Then do an update on this select...something like:

UPDATE PARTS SET NAME = 'CORRECT NAME' WHERE PART_NUMBER IN (SELECT NAME FROM PARTS WHERE PART_NUMBER LIKE '6JH%[ODD OR SOMTHING]';

Do you see what I mean now?

 

[karluk]

The substr should still work. You just need to substitute the correct column name. Unless I'm missing something, your update statement should be

UPDATE PARTS SET NAME = 'CORRECT NAME' WHERE SUBSTR(PART_NUMBER,5,1) IN ('1','3','5','7','9');

 

[shagymoe]

Hey that worked man...thanks a lot!! I'm new to sql commands and I have looked all over the web for this type of info.....do you know a good place for more advanced info? Most of what I have found is basic and doesn't tell things like this. Thanks again.

 

[THoey]

Shagy,

You have found the place... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[MalcolmW]

Instead of using timestamps, in which you run the risk of your server being too fast and stamping multiple records with the same timestamp, you could just number the records. A simple way to do this in SQL Server is:

declare @variable int
set @variable = 0
update table
SET @variable = newcolumn = @variable + 1

the SET statement is read Japanese style, right to left.
First the column is assigned the value of the variable plus one, then the variable is assigned the new value of the column. The process repeats in a single pass for the whole table. You can also use this for running totals.
This may work for Sybase as well because of the common heritage - but haven't tried it.

Gimmicks aside, what you've got is a classic situation for using a cursor - and it is simple enough that to be a good one to learn to use cursors with. Malcolm