Issue comparing alphanumeric fields

Question

Post reply

Issue comparing alphanumeric fields

Harvey-430101

SSC Enthusiast

Points: 156
More actions
May 1, 2007 at 2:04 pm

#118441

I'm expecting one record to be returned during the compare but two are coming back.
Here's the table, both fields are varchar(8)
FROMACCT     TOACCT
09100A           09100Z
091000           091499
My select is looking for 09100U which should fall between 09100A and 09100Z but both records are being returned.
Is there anyway to get this compare to work correctly?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

Greg Charles

SSC-Forever

Points: 45403

May 1, 2007 at 2:27 pm

#703658

Please post your SELECT statement.

Thanks,

Greg

Harvey-430101 SSC Enthusiast Points: 156 More actions · Answer 2

select * from tablename where '09100U' Between FROMACCT and TOACCT

Sergiy SSC Guru Points: 110208 More actions · Answer 3

So, what's wrong?

'09100U' >= '091000' - TRUE

'09100U' <= '091499' - TRUE

Line to be returned.

_____________
Code for TallyGenerator

Harvey-430101 SSC Enthusiast Points: 156 More actions · Answer 4

There's nothing wrong with what's being returned, SQL is doing exactly what I am asking it to do. My issue is I want the first record returned and not the second. I don't know how to get this to happen. I'm looking for the best match not all the matches and the best match is between 09100A and 09100Z.

Sergiy SSC Guru Points: 110208 More actions · Answer 5

From .. To range from second row covers whole range from 1st row.

So, the task as you describe it looks like MI-IV.

Probably if you try to explain what are you trying to achieve with what kind of data somebody could help you.

_____________
Code for TallyGenerator

Harvey-430101 SSC Enthusiast Points: 156 More actions · Answer 6

I'm working with an extract from peoplesoft and I have no control over the actual data itself or the design of the database. I ended up inserting all the records into a table and removed the duplicates. Not exactly what I was hoping for but it will work for this process.

Thanks.

Sergiy SSC Guru Points: 110208 More actions · Answer 7

Oh, Peplesoft... Well, well.

Heard it somewhere. Probably when I was doing extract for importing data into Peoplesoft system.

What I can suggest is to dismantle those codes into parts.

It seems code consists from 2 parts: 3 symbols for some kind of group, last - identifier within groups.

But you better ask for real meaning of those symbols those who sends you data.

After you separate parts of codes you may distinguish numeric identifers (000-499) from alphabetical (00A-00Z, which actually must be convrted to A-Z, I guess).

After that you not gonna have problems with placing your code "09100U" into the right range.

_____________
Code for TallyGenerator

Ram-425798 Right there with Babe Points: 798 More actions · Answer 8

you can try this... c1, c2, c3 are columns.....

SELECT c1, c2, c3

FROM table

WHERE PATINDEX('%[a-z0-9]%', c1)

BETWEEN PATINDEX('%[a-z0-9]%', c2)

AND PATINDEX('%[a-z0-9]%',c9)

Hope this works.....

samrit

Ram-425798 Right there with Babe Points: 798 More actions · Answer 9

you can try this... c1, c2, c3 are columns.....

SELECT c1, c2, c3

FROM table

WHERE PATINDEX('%[a-z0-9]%', c1)

BETWEEN PATINDEX('%[a-z0-9]%', c2)

AND PATINDEX('%[a-z0-9]%',c9)

Hope this works.....

samrit