SQL Select Statement

[1SorryDog]

This maybe a dummy question but here goes....

Not sure which SQL forum to post this question but...
I have a select statement for an ORACLE (9i) database similar to this:

SELECT * FROM MY_TABLE WHERE DESCRIPTION = '1234-GEORGE'

The DESCRIPTION field is defined as variable charater. It seems that the minus sign in killing the select statement. No rows returned.

However this works:

SELECT * FROM MY_TABLE WHERE DESCRIPTION LIKE '1234%'

But, I need the remaining charaters after the dash as they can be anything, for exapmle DESCRIPTION = '1234-TOMMY'

Is there any way for sql to see the minus sign as a dash or string charater instead of a minus sign???

Any suggestions would be appreciated.

One last note, the minus sign can't be eliminated as this is as example of a part number.


Thanks.


SorryDog

 

[SantaMufasa]

SorryDog,

Can you please post what results from this query:

SELECT description FROM my_table WHERE description LIKE '1234%';

Those results will tell us all we need to know so that we can help you formulate the proper query.


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[SantaMufasa]

BTW, I'm in transit between remote site and home for the next 20 minutes. I'll post a response as soon as I'm back on net.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[1SorryDog]

Mufasa,

Thanks for your response.

The first select statement above returns no rows.

The second select statement: SELECT * FROM MY_TABLE WHERE DESCRIPTION LIKE '1234%' returns the correct row, but I need the remaining text including the minus sign compared as well. Simply put, a string to string comparison which treats the minus sign as a charater or dash.

Currently i'm using Toad to test my select query, but eventually it will be in one of my vb apps.

Thanks for your help.

SorryDog

 

[SantaMufasa]

Okay now, SorryDog, everything you mentioned in your most recent post, I understood before my most recent post...I really need you to post the results of the query (or counterpart to it) that I posted "7 Jun 05 21:55"...I need to see the actual contents of DESCRIPTION to be able to explain to you why your first query returns no rows and how to get it to work properly.

Looking forward to seeing your results,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[1SorryDog]

SantaMufasa,

Ok. I'll try as close as I can. The DESCRIPTION field is defined as varchar with a length of 30. The contents of one of the fields would look something like:

1234MM020000005 RR0200-MEAT

The above example is not an actual part number, but that is the format of the part number which is stored in the DESCRIPTON field. The length of the string is always the same length.

Maybe the space combined with the minus sign is the problem ??? Or is it the space??

Thanks again for your help.

SorryDog

 

[SantaMufasa]

SorryDog,

Here are sample data for MY_TABLE:

select description from my_table;

DESCRIPTION
------------------------------
1234MM020000005 RR0200-MEAT
1234MM020000005 RR0200-FISH

Here is proof of concept that you can query using a comparison that includes both space and '-' characters:

select * from my_table where description = '1234MM020000005 RR0200-MEAT';

DESCRIPTION
------------------------------
1234MM020000005 RR0200-MEAT

Now, how does my query differ from your query?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[1SorryDog]

SanatMufasa,

I finally found the problem! My query was working as it should.

I found a CR or char(13) attached at the end of each string in my DESCRIPTION field. So in effect the string by string comparison wasn't matching and therefore returned no rows.

I found it by pulling up sql plus then ran a query to list all DESCRIPTION rows of MY_TABLE. At the end of each line was a square black box.

I appreciate all your help. You convinced me that the minus sign wasn't interfering with my query.


SorryDog