August 10, 2011 at 2:02 pm
Does anyone know why numbers very close to 1000000 and 2000000 would be have strangly with a length function?
when I run
SELECT *
FROM TESTreg
WHERE len (idnumber) <7
two of the results I get are 1000004 and 1999996
However
SELECT len (1000004) returns 7
August 10, 2011 at 2:08 pm
What's the datatype of the id?
Are you sure this is the actual statement you are running? That just doesn't seem to be possible.
August 10, 2011 at 2:11 pm
That's... odd.
Can you provide the ddl of the table you're reading from, and can you recreate it in a test script that isn't using the currently existing misbehaving table?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 10, 2011 at 2:11 pm
the data type is float. I am sure it's the actual line.
I ran a function on over 800,000 rows and only these two numbers has this weird aspect to them, so I pulled them into a test table to play with.
August 10, 2011 at 2:12 pm
MattLearningSQL (8/10/2011)
the data type is float. I am sure it's the actual line.I ran a function on over 800,000 rows and only these two numbers has this weird aspect to them, so I pulled them into a test table to play with.
Ugh. Yeah, Float rounds. I'd have to play with it a bit to find equivalent issues but I could see this occurring for the one near 1000000. The one up by 2000000 doesn't make any sense though.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 10, 2011 at 2:17 pm
Can you post the function?
Second question: is there any specific reason to use FLOAT instead of NUMERIC() or DECIMAL() (or even INT)?
August 10, 2011 at 2:19 pm
Easy test, do a select into of that column in a temp table but convert to int in the process then rerun your where. You should see the correct results.
You would also see correct results and have a sargable filter by doing where id < 1 000 000 (instead of len())
August 10, 2011 at 2:22 pm
The funtion I needed was
UPDATE registrations
SET idnummod = stuff ('0000000', (8 - len(idnumber)), len(idnumber), idnumber)
FROM registrations
WHERE len(idnumber) < 7
After converting the float, I was able to get
UPDATE registrations
SET idnummod = stuff ('0000000', (8 - len(convert (int, idnumber))), len(convert (int,idnumber)), convert (int, idnumber))
FROM registrations
WHERE len (idnumber) <7
This dealt with the problem completely! Thanks for all the input.
August 10, 2011 at 2:29 pm
Lutz,
The data was only in float because that is the format it is imported in.
Craig,
The ddl was transact SQL I think? It's sql server 2005.
Thanks again for all the input guys!
August 10, 2011 at 2:32 pm
ddl = data definition language. Basically the script to recreate the table in question in this case.
We would have seen float for the id and spotted the problem in 2 seconds, rather than guessing at it.
This was an "easy" one to guess so NP ;-). Easy only because nothing else made any kind of sense.
August 10, 2011 at 2:33 pm
instead of using LEN, you could get the same result with (assuming the purpose is to add leading zeros)
SELECT RIGHT(10000000 + CAST(idnumber as INT),7)
where id < 1 000 000
Edit: typo fixed and CAST added
August 10, 2011 at 2:38 pm
Why bother with right?
SELECT 1 000 000 + CONVERT(INT, ID) WHERE ID < 1 000 000
August 10, 2011 at 2:47 pm
Ninja's_RGR'us (8/10/2011)
Why bother with right?SELECT 1 000 000 + CONVERT(INT, ID) WHERE ID < 1 000 000
Because it would not return a character value with leading zeros?
The function Matt posted would return a 7 character long value with leading zeros and the converted integer value.
Of course, this implies idnummod being a character data type column and not any kind of numeric data type. 😉
August 10, 2011 at 2:52 pm
LutzM (8/10/2011)
Ninja's_RGR'us (8/10/2011)
Why bother with right?SELECT 1 000 000 + CONVERT(INT, ID) WHERE ID < 1 000 000
Because it would not return a character value with leading zeros?
The function Matt posted would return a 7 character long value with leading zeros and the converted integer value.
Of course, this implies idnummod being a character data type column and not any kind of numeric data type. 😉
Had forgotten the function and was focussing simply on your operation!
Another reason to post DDL! 😀
August 10, 2011 at 2:58 pm
I needed to pad anything with less than 7 digits because the check function I had to use returned different results on padded versus unpadded.
Yes, the format for the padding is character rather than number so the padding doesn't disappear.
It's all up and running now!
I know this is a silly question, but how do I post in ddl? Something like ?
I understand what a ddl is, but not how to post code in one here.
I imagine I'll be posting another question on this forum at some point, so I thought I would ask.
Thanks again for all the input.
I'll try to redesign the stored query based on the changes, but I had to get the existing data done today, so used my (admittedly ugly) way.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply