Trying to use WHERE statement on char column with '-' in? 1

[jatkinson]

Hi,

First of all let me apologise if I'm in the wrong place and for the naivity of my question, I'm new to SQL queries etc, so please do point me in the right direction of the correct forum if this isn't right.

Basically, a product we use stores records into a field in the format 12345-12345. The field is described as PK, Char(16), not null.

What I simply want to do is apply a WHERE statement to a query that allows me find a specific value, however it doesn't work and I'm guessing the presence of the '-' could be the reason why?

The error I receive is as follows:

Syntax error converting the varchar value ' 1-00001' to a column of data type int.

Can anyone please advise how I can get round this if possible?

Regards,

J

 

[PHV]

What is your actual WHERE clause raising the error ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

My guess is a lack of quoting:
WHERE yourPKcolumn=[!]'[/!]12345-12345[!]'[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jatkinson]

For example I want to search for an entry with the value 12345-00001 so I used the following:

WHERE tablename.fieldname = 12345-00001

 

[jatkinson]

Hi, I did think of that and used:

WHERE tablename.fieldname = '12345-00001'

That simply returned no entries even though I can see the value clear as day in the open table.

Hence a newbie getting confused! ;o)

 

[PHV]

The field is described as PK, Char([!]16[/!]), not null
So, perhaps you have to pad your string literal:
WHERE tablename.fieldname = '12345-00001 '

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jatkinson]

Aah, I see what you're saying I'll give that a whirl!

Cheers.

 

[jatkinson]

Hi,

That didn't seem to work either.

Interestingly I thought of using LIKE so I played with various but couldn't get that to work. The only result that gave anything was the following

WHERE tablename.fieldname LIKE '%1-00001' but that then gave me entries for

1-00001
11-00001
21-00001
31-00001

etc etc, which makes sense, but I can't understand how to get the one result. Maybe the padding needs to go at the front?

 

[jatkinson]

Yep, that does it!!!

If I pad it out at the front to make 16 characters it works!!

Thanks for the pointer!

Next question then, is it possible, to code the query so I can strip the extra characters out and just type in the numbers I want to search by rather than padding it out into the query?

 

[PHV]

The standard way:
WHERE TRIM(tablename.fieldname) = '12345-00001'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jatkinson]

Thanks PHV, thats great!