General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
>I have a field in a table with values like "T1110X" and "T1110.12". I want to extract just the number part, which always starts on space 2. So I do ?TRAN(VAL(SUBS(myField,2))). For each field it works great showing: "1110" and "1110.12" respectively. Here's the weird part: If I use a SELECT statement: SELECT TRAN(VAL(SUBS(myField,2))) FROM myTable WHERE !empty(myField) -- for the same records I get "11" and "11". Why wouldn't this work? As a work around I am doing: SELECT LEFT(TRAN(VAL(SUBS(myField,2))),15)..... and it works fine. Can anyone teach me something here? I figure I wasted so much time on this, I should at least learn something out of it!
>
>TIA,
>Marcus.
well...
1. If you don't pad the field then the first record selected will determine the length of the field... so, if your first record is....
U11
and your second is...
T1123
YOu will get 11 for both, since the field in your cursor is going to be lenght of two.
Also, if you are looking for character, why are you using VAL?
What the left statment is doing is padding all values to a length of 15, so that there is room to hold all the values.
Look at your first record and see if it is a two digit length.
BOb
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only