ODBC Text problem

[tonyweis138]

I have a customer that is scanning in a value (ex. 12345.00) that needs to be matched to a value in a text file to return a row.

However, the key value in the row would be 12345 without the '.00'. My problem is how to strip off that last few characters in the string.

If i was doing it with sql server i could do something like:

select * from table where KeyField = substring(value, 0,charindex('.',value))

But with the limited statements available with the MS ODBC text driver, i am not sure how to do it. Any ideas?

 

[jdemmi]

I think you can use the SQL you have provided to parse the file in question.

http://support.microsoft.com/kb/178717

(it's not listed in the unsupported SQL section in the above KB)

-- Jason
"It's Just Ones and Zeros

 

[tonyweis138]

It isn't listed, but it gives me errors stating that those are unrecognized commands.

 

[jdemmi]

Will this help?

http://support.microsoft.com/kb/135796

-- Jason
"It's Just Ones and Zeros

 

[tonyweis138]

Awesome, that is half the battle. Now i just need to find a function to find the position of the '.' in the string in order to know where it should be split. That Mid() works great, though.

 

[k5tm]

That might be LOCATE(), which is the function listed in the ODBC 2.0 specification. (Of course, the specification also lists SUBSTRING()).

Tom Morrison

http://www.liant.com