Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Feb 2005
    Posts
    4

    Unanswered: 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?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd convert the single digit numbers to a more managable form, something like:
    Code:
       ORDER BY CASE WHEN GarmentSize LIKE '[0-9]'
       THEN ' ' + GarmentSize ELSE GarmentSize END
    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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:
    Code:
    CASE WHEN IsNumeric(GarmentSize)
       THEN Cast(Cast(GarmentSize AS MONEY)) AS CHAR(50))
       ELSE GarmentSize END
    -PatP

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    varchar?...if we're changing datatypes, why not santize the data and make it int?

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2005
    Posts
    4
    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.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r937
    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/ar...6/09/1530.aspx


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

    Now aint that nitpicky...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    Last edited by r937; 02-07-05 at 14:35.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
      SELECT GarmentSize 
        FROM PHOOLRATIOS 
    ORDER BY  CASE WHEN ISNUMERIC(GarmentSize) = 1 
    	       THEN CONVERT(int, GarmentSize) 
    	       ELSE 0 
    	  END
    	, GarmentSize
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r937
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry, that was my bad! How about:
    Code:
    Select GarmentSize
       from PHOOLRATIOS
       order by CASE WHEN IsNumeric(GarmentSize) = 1
          THEN Cast(Cast(GarmentSize AS integer)) AS CHAR(50))
          ELSE GarmentSize
       END
    -PatP

  13. #13
    Join Date
    Feb 2005
    Posts
    4
    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'.

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    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
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    brett obviously is dropping his myTable99 before looking at the results

    this --
    Code:
    Select GarmentSize
       from myTable99
       order by CASE WHEN IsNumeric(GarmentSize) = 1
          THEN Cast(Cast(GarmentSize AS integer) AS CHAR(50))
          ELSE GarmentSize
       END
    does 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.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •