Cast as Integer problem.

I have DB that contains a field named GarmentSize. The field type is TEXT as it can contain for example

8
10
12
14

or

L
XL
XXL

The problem is when I run a query and order by GarmentSize the results displayed are as follows if it contains numbers

10
12
14
8

Instead of

8
10
12
14

If I use order by CAST(GarmentSize as Integer) this works fine, unless the field contains text then is throws the following error.

Syntax error converting the varchar value 'XL' to a column of data type int

Is there a way of determining if the field contains characters that can be casted before doing the CAST?I'd convert the single digit numbers to a more managable form, something like: ORDER BY CASE WHEN GarmentSize LIKE '[0-9]'
THEN ' ' + GarmentSize ELSE GarmentSize END-PatP|||yeah, but pat, that still doesn't solve the sequencing problem

appending '42' to a space is still gonna come after appending '105' to a space

gosman, i urge you most strenuously, downsize your TEXT field to at least VARCHAR(8000)

in fact, i would take bets that you could probably downsize it to VARCHAR(100) and still accommodate all the GarmentSizes in your database|||Wouldn't a size 42 have to be about five meters tall, and weigh 1000 Kg or more? ...and I thought that Americans were big! I'm not sure I'm prepared to explore the size 105.

To futher address this problem so it can handle more cases, how about:CASE WHEN IsNumeric(GarmentSize)
THEN Cast(Cast(GarmentSize AS MONEY)) AS CHAR(50))
ELSE GarmentSize END-PatP ;)|||varchar?...if we're changing datatypes, why not santize the data and make it int?

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 text)
GO

INSERT INTO myTable99(Col1)
SELECT 'XL' UNION ALL
SELECT '1'
GO

SELECT CASE WHEN ISNUMERIC(CONVERT(varchar(8000),Col1)) = 0 THEN 'NO' ELSE 'YES' END AS [A Number?], Col1
FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||sanitize the data and make it integer? hitting the 'ritas at lunch again, were we?

what do you propose as integer equivalents of XS, S, M, L, XL?

oh, oh, oh, please say "use foreign keys to look up the size name"

:) :) :)|||Thanks Guys.

Pat the field type is actually VARCHAR 50

I've tried the following

Select GarmentSize from PHOOLRATIOS order by CASE WHEN IsNumeric(GarmentSize)THEN Cast(Cast(GarmentSize AS integer)) AS CHAR(50))ELSE GarmentSize END

But I get this message. Incorrect syntax near the keyword 'THEN'.

Forgive my ignorance but I'm a complete SQL novice.|||sanitize the data and make it integer? hitting the 'ritas at lunch again, were we?

what do you propose as integer equivalents of XS, S, M, L, XL?

oh, oh, oh, please say "use foreign keys to look up the size name"

:) :) :)

I would say that the data is not normalized, since XL is a representation of a size range, not the actuall size...

And the Foreign key bit for a surrogate

puuuuleeeze

http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx

You might want a "size fits from" and a "size fits to" columns

Now aint that nitpicky...|||gosman, you lied, your first post distinctly says TEXT :)

try WHEN IsNumeric(GarmentSize) = 1 THEN ...

sorry, brett, garmentsize is normalized

if a range is your criterion of not normalized, then any of your tables that uses a datetime column is not normalized, since we all know that a datetime value is not a single value but rather represents a range

;)|||SELECT GarmentSize
FROM PHOOLRATIOS
ORDER BY CASE WHEN ISNUMERIC(GarmentSize) = 1
THEN CONVERT(int, GarmentSize)
ELSE 0
END
, GarmentSize|||gosman, you lied, your first post distinctly says TEXT :)

try WHEN IsNumeric(GarmentSize) = 1 THEN ...

sorry, brett, garmentsize is normalized

if a range is your criterion of not normalized, then any of your tables that uses a datetime column is not normalized, since we all know that a datetime value is not a single value but rather represents a range

;)

Who's been drinking at lunch?|||Sorry, that was my bad! How about:Select GarmentSize
from PHOOLRATIOS
order by CASE WHEN IsNumeric(GarmentSize) = 1
THEN Cast(Cast(GarmentSize AS integer)) AS CHAR(50))
ELSE GarmentSize
END-PatP|||Hi Pat

Select GarmentSize
from PHOOLRATIOS
order by CASE WHEN IsNumeric(GarmentSize) = 1
THEN Cast(Cast(GarmentSize AS integer)) AS CHAR(50))
ELSE GarmentSize
END

Server: Msg 1035, Level 15, State 10, Line 4
Incorrect syntax near 'Cast', expected 'AS'.|||USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(GarmentSize char(50))
GO

INSERT INTO myTable99(GarmentSize)
SELECT 'XL' UNION ALL
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '4' UNION ALL
SELECT '5' UNION ALL
SELECT '6' UNION ALL
SELECT '10' UNION ALL
SELECT '11' UNION ALL
SELECT '112'
GO

SELECT GarmentSize
FROM myTable99
ORDER BY CASE WHEN ISNUMERIC(GarmentSize) = 1
THEN CONVERT(int, GarmentSize)
ELSE 0
END
, GarmentSize

--Pat's off his meds..forgive him

Select GarmentSize
from myTable99
order by CASE WHEN IsNumeric(GarmentSize) = 1
THEN Cast(Cast(GarmentSize AS integer) AS CHAR(50))
ELSE GarmentSize
END

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||brett obviously is dropping his myTable99 before looking at the results

this --Select GarmentSize
from myTable99
order by CASE WHEN IsNumeric(GarmentSize) = 1
THEN Cast(Cast(GarmentSize AS integer) AS CHAR(50))
ELSE GarmentSize
ENDdoes not sort the "numeric" values into numeric sequence!!!

insert the value '42' and you'll see what i mean

and yes, every datetime value represents a range

and i don't start drinking until after 5:00 p.m.

:) :) :)|||Well I'll be a Hunkey's Monkle! Brett is correct.

It appears that CAST does a right fill with spaces. My example ought to read:SELECT GarmentSize
FROM PHOOLRATIOS
ORDER BY CASE WHEN 1 = IsNumeric(GarmentSize)
THEN Str(GarmentSize, 50)
ELSE GarmentSize
ENDThe alternative that he posted would also be good, although I'd reverse the order to put the numeric values first, something like:SELECT GarmentSize
FROM myTable99
ORDER BY CASE WHEN ISNUMERIC(GarmentSize) = 1
THEN 1
ELSE 2
END, GarmentSize-PatP|||brett obviously is dropping his myTable99 before looking at the results

this --Select GarmentSize
from myTable99
order by CASE WHEN IsNumeric(GarmentSize) = 1
THEN Cast(Cast(GarmentSize AS integer) AS CHAR(50))
ELSE GarmentSize
ENDdoes not sort the "numeric" values into numeric sequence!!!

insert the value '42' and you'll see what i mean

and yes, every datetime value represents a range

and i don't start drinking until after 5:00 p.m.

:) :) :)

Rudy...ya gotta stop smokin crack...it's not good for you...

Yes, I know..that's Pat's query...did you see the first query?

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(GarmentSize char(50))
GO

INSERT INTO myTable99(GarmentSize)
SELECT 'XL' UNION ALL
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '4' UNION ALL
SELECT '5' UNION ALL
SELECT '6' UNION ALL
SELECT '10' UNION ALL
SELECT '40' UNION ALL
SELECT '11' UNION ALL
SELECT '112'
GO

SELECT GarmentSize
FROM myTable99
ORDER BY CASE WHEN ISNUMERIC(GarmentSize) = 1
THEN CONVERT(int, GarmentSize)
ELSE 0
END
, GarmentSize

SET NOCOUNT OFF
DROP TABLE myTable99
GO

And what's with the very specific point in time being a range?|||sorry, pal, i don't do crack either

keep fishing, you're sure to hit one of my vices eventually

there is no such thing as a point in time

what i was trying to get you to see goes something like this --

if A implies B, and C is A, then C implies B

A = a range
B = not normalized
C = some value

you claimed a range was not normalized

i'm saying that by this very same logic, since a datetime value is a range, therefore any of your tables using a datetime value is not normalized

actually, i have no problem with datetime values, and i know exactly how to handle them

what i was trying to point out is that your claim that a size is actually a range and therefore not nomalized is nonsense

no offence, good buddy, but it was

and if you were only joking, then you simply must learn how to use smileys correctly|||It appears that CAST does a right fill with spaces. My example ought to read...It's not the CAST, it's the datatype that you use within the CAST, - CHAR(50). If you used VARCHAR(50) there won't be any "right fill"-ing ;)|||OK...attempt to be clear.

Our friends Column, GarmentSize, is retaining 2 types of data in the same column.

One is the very specific size of a garmet, let's say a size 10.

Recording the size of the garment as M or medium presents no specific size. M is a range of sizes, like 10-12. Not 1 size.

Apples and Oranges. They're both fruit, but they are essentially different.

And I did say I was being picky, but I'd still say they'd need another table that addresses this and descibes what each (M, L, XL, ect) actually means.

I still don't get what you mean by that a datetime column represents a range.

I never said a "range was not normalized". It's the combination of a specific size and a range of sizes (XL, for example) is not normalized. The size range values in their own column would be fine.

OK, Vices, how about Fishing?|||yes, XL is a range, it is the range between where L leaves off and XXL begins

just like size 8 is a range, between where 7 leaves off and 9 begins!!

nope, i don't fish, nor do i hunt

i do like baiting gullible DBAs, though, and you are providing a world of entertainment for us today!!

have you ever gone into the Senior VP of Marketing's office and told her she cannot use XL as a size, and should use a numeric range instead?

what utter nonsense

"The size range values in their own column would be fine" -- they already are in their own column, it's called GarmentSize, and it has a perfectly valid domain consisting of sizes which happen to be either numbers or letters

but you DBAs don't know what a domain is, so let's leave the modelling nuances alone and go back to talking about other stuff like raid stripes and scuzzies and other important stuff

:)|||Then it's got to be some sort of hallucinogen

Is an apple a range, is an airplane a range, is a shirt a range?

Isn't our freinds problem proof enough that their data is not normalized?|||Is an apple a range, is an airplane a range, is a shirt a range?no, they are not

Isn't our freinds problem proof enough that their data is not normalized?no, the fact that numerics in a VARCHAR column sort as characters is certainly not proof that the data is not normalized

i hesitate to ask, but do you even know what normalized means?

like i suggested in my last post, perhaps (for your sake) we should just drop the subject|||Scrapped! Looked good on paper...|||OK, try again:

ORDER BY right('000000000...50 of these...0000000' + GarmentSize, 50)|||Since the data itself is not going to be sorted very well, dare I suggest a sortorder column? This may curdle Rudy's blood, but hey, why should he have all the fun, eh? ;-)|||fantastic idea!! a sortorder column!!

okay, now, let's see, how should we populate this column

I KNOW!! let's make it numberic, and for "numeric" sizes, we'll use the actual numbers!!

now, what about those pesky alpha sizes, like S, M, XL, and so on...

HEY!! BRILLIANT IDEA!! let's assign S to 7, M to 9, XL to 14, ...

uh oh, wait a sec

that would make a man's XL shirt, which is approximately a chest 42" the same as a woman's XL dress, which is a size 6...

brett? where are you, brett? could you please normalize this sortorder column for us?

pretty please?|||You just aren't seeing it Rudy..

XL is not 1 size.

And I gotta admit, you're a little edgy today...

In pants a men's XL would be 38-40...

woops, gotta run...

Don't forget your midol