> SELECT top 100
>  ltrim(rtrim(UPC)) as UPC
> ,CAST(rtrim(HEIGHT) AS INT) as H
> ,CAST(rtrim(WIDTH) AS INT) as W
> ,CAST(rtrim(DEPTH) AS INT) as D
> FROM  brdatadb.dbo.Item
> WHERE height >= '0'
> ORDER BY upc
>
> Replace INT with whatever number datatype you need.
>
> SQL will generate an error if the text contains something that cannot be
> converted to the number you want.
>
> Tom

"Dan" <dan.ga***@gmail.com> wrote in message
news:f555dc97-d4d2-4fc8-94de-47890bb81a7d@z28g2000prd.googlegroups.com...
> Hello -
>
> I have the following query which is need of converting the Height,
> Width, Depth data into a number.  It is currently being stored as text
> with extra spaces at the end in the database it comes from.  Any
> thoughts?
>
>
> SELECT top 100     ltrim(rtrim(UPC)) as UPC, rtrim(HEIGHT) as H,rtrim
> (WIDTH) as W,rtrim(DEPTH) as D
> FROM  brdatadb.dbo.Item
> WHERE height >= '0'
> ORDER BY upc
>
>
> Thanks for any help!
> Dan

> Dan,
>
> You can use the CAST() function.  There is also a CONVERT() function, but I
> like CAST better than CONVERT.  Look both of them up in Help/BOL.
>
> What kind of number data type do you need?
>
> /*** Your Original Script ***/
> SELECT top 100
>         ltrim(rtrim(UPC)) as UPC
>     ,    rtrim(HEIGHT) as H
>     ,    rtrim(WIDTH) as W
>     ,    rtrim(DEPTH) as D
> FROM
>         brdatadb.dbo.Item
> WHERE
>         height >= '0'
> ORDER BY
>         upc
>
> Here are a some options:
>
> SELECT top 100
>         ltrim(rtrim(UPC)) as UPC
>     ,    CAST(rtrim(HEIGHT) as int) as H
>     ,    CAST(rtrim(WIDTH) as float) as W
>     ,    CAST(rtrim(DEPTH) as decimal(5,2)) as D
>
> Hope this helps,
>
> Conan
>
> "Dan" <dan.ga***@gmail.com> wrote in message
>
> news:f555dc97-d4d2-4fc8-94de-47890bb81a7d@z28g2000prd.googlegroups.com...
>
>
>
> > Hello -
>
> > I have the following query which is need of converting the Height,
> > Width, Depth data into a number.  It is currently being stored as text
> > with extra spaces at the end in the database it comes from.  Any
> > thoughts?
>
> > SELECT top 100     ltrim(rtrim(UPC)) as UPC, rtrim(HEIGHT) as H,rtrim
> > (WIDTH) as W,rtrim(DEPTH) as D
> > FROM  brdatadb.dbo.Item
> > WHERE height >= '0'
> > ORDER BY upc
>
> > Thanks for any help!
> > Dan- Hide quoted text -
>
> - Show quoted text -

> Dan,
>
> You can use the CAST() function. There is also a CONVERT() function, but I
> like CAST better than CONVERT. Look both of them up in Help/BOL.
>
> What kind of number data type do you need?
>
> /*** Your Original Script ***/
> SELECT top 100
> ltrim(rtrim(UPC)) as UPC
> , rtrim(HEIGHT) as H
> , rtrim(WIDTH) as W
> , rtrim(DEPTH) as D
> FROM
> brdatadb.dbo.Item
> WHERE
> height >= '0'
> ORDER BY
> upc
>
> Here are a some options:
>
> SELECT top 100
> ltrim(rtrim(UPC)) as UPC
> , CAST(rtrim(HEIGHT) as int) as H
> , CAST(rtrim(WIDTH) as float) as W
> , CAST(rtrim(DEPTH) as decimal(5,2)) as D
>
> Hope this helps,
>
> Conan
>
> "Dan" <dan.ga***@gmail.com> wrote in message
>
> news:f555dc97-d4d2-4fc8-94de-47890bb81a7d@z28g2000prd.googlegroups.com...
>
>
>
> > Hello -
>
> > I have the following query which is need of converting the Height,
> > Width, Depth data into a number. It is currently being stored as text
> > with extra spaces at the end in the database it comes from. Any
> > thoughts?
>
> > SELECT top 100 ltrim(rtrim(UPC)) as UPC, rtrim(HEIGHT) as H,rtrim
> > (WIDTH) as W,rtrim(DEPTH) as D
> > FROM brdatadb.dbo.Item
> > WHERE height >= '0'
> > ORDER BY upc
>
> > Thanks for any help!
> > Dan- Hide quoted text -
>
> - Show quoted text -

> Dan,
>
> In your reply to Tom's reply, you said your error was...
>
> Msg 245, Level 16, State 1, Line 1
> Conversion failed when converting the varchar value '6.5' to data type
> int
>
> Just guessing...maybe SQL Server doesn't like converting 6.5 (a
> decimal/float/real) to an integer.
>
> If you look at my original reply, I gave you 3 different examples:
>
> Height was converted to an integer,
> Width was converted to a float,
> Depth was converted to a decimal.
>
> Choose the most appropriate data type.
>
> Once again, I'm just guessing...I did not test this out.
>
> Experiment with the different data types to see which one will work for you.
>
> HTH,
>
> Conan
>
> "Dan" <dan.ga***@gmail.com> wrote in message
>
> news:d2a85ea0-6644-4f84-9dda-aeec1d0200e9@40g2000prx.googlegroups.com...
> On Dec 11, 5:07 pm, "Conan Kelly"
>
>
>
>
>
> <CTBarbarinNOS...@msnNOSPAM.comNOSPAM> wrote:
> > Dan,
>
> > You can use the CAST() function. There is also a CONVERT() function, but I
> > like CAST better than CONVERT. Look both of them up in Help/BOL.
>
> > What kind of number data type do you need?
>
> > /*** Your Original Script ***/
> > SELECT top 100
> > ltrim(rtrim(UPC)) as UPC
> > , rtrim(HEIGHT) as H
> > , rtrim(WIDTH) as W
> > , rtrim(DEPTH) as D
> > FROM
> > brdatadb.dbo.Item
> > WHERE
> > height >= '0'
> > ORDER BY
> > upc
>
> > Here are a some options:
>
> > SELECT top 100
> > ltrim(rtrim(UPC)) as UPC
> > , CAST(rtrim(HEIGHT) as int) as H
> > , CAST(rtrim(WIDTH) as float) as W
> > , CAST(rtrim(DEPTH) as decimal(5,2)) as D
>
> > Hope this helps,
>
> > Conan
>
> > "Dan" <dan.ga***@gmail.com> wrote in message
>
> >news:f555dc97-d4d2-4fc8-94de-47890bb81a7d@z28g2000prd.googlegroups.com....
>
> > > Hello -
>
> > > I have the following query which is need of converting the Height,
> > > Width, Depth data into a number. It is currently being stored as text
> > > with extra spaces at the end in the database it comes from. Any
> > > thoughts?
>
> > > SELECT top 100 ltrim(rtrim(UPC)) as UPC, rtrim(HEIGHT) as H,rtrim
> > > (WIDTH) as W,rtrim(DEPTH) as D
> > > FROM brdatadb.dbo.Item
> > > WHERE height >= '0'
> > > ORDER BY upc
>
> > > Thanks for any help!
> > > Dan- Hide quoted text -
>
> > - Show quoted text -
>
> Darn, same error with your suggestion Conan.  :(- Hide quoted text -
>
> - Show quoted text -

> On Dec 11, 5:29 pm, "Conan Kelly"
> <CTBarbarinNOS...@msnNOSPAM.comNOSPAM> wrote:
>> Dan,
>>
>> In your reply to Tom's reply, you said your error was...
>>
>> Msg 245, Level 16, State 1, Line 1
>> Conversion failed when converting the varchar value '6.5' to data type
>> int
>>
>> Just guessing...maybe SQL Server doesn't like converting 6.5 (a
>> decimal/float/real) to an integer.
>>
>> If you look at my original reply, I gave you 3 different examples:
>>
>> Height was converted to an integer,
>> Width was converted to a float,
>> Depth was converted to a decimal.
>>
>> Choose the most appropriate data type.
>>
>> Once again, I'm just guessing...I did not test this out.
>>
>> Experiment with the different data types to see which one will work for you.
>>
>> HTH,
>>
>> Conan
>>
>> "Dan" <dan.ga***@gmail.com> wrote in message
>>
>> news:d2a85ea0-6644-4f84-9dda-aeec1d0200e9@40g2000prx.googlegroups.com...
>> On Dec 11, 5:07 pm, "Conan Kelly"
>>
>>
>>
>>
>>
>> <CTBarbarinNOS...@msnNOSPAM.comNOSPAM> wrote:
>>> Dan,
>>> You can use the CAST() function. There is also a CONVERT() function, but I
>>> like CAST better than CONVERT. Look both of them up in Help/BOL.
>>> What kind of number data type do you need?
>>> /*** Your Original Script ***/
>>> SELECT top 100
>>> ltrim(rtrim(UPC)) as UPC
>>> , rtrim(HEIGHT) as H
>>> , rtrim(WIDTH) as W
>>> , rtrim(DEPTH) as D
>>> FROM
>>> brdatadb.dbo.Item
>>> WHERE
>>> height >= '0'
>>> ORDER BY
>>> upc
>>> Here are a some options:
>>> SELECT top 100
>>> ltrim(rtrim(UPC)) as UPC
>>> , CAST(rtrim(HEIGHT) as int) as H
>>> , CAST(rtrim(WIDTH) as float) as W
>>> , CAST(rtrim(DEPTH) as decimal(5,2)) as D
>>> Hope this helps,
>>> Conan
>>> "Dan" <dan.ga***@gmail.com> wrote in message
>>> news:f555dc97-d4d2-4fc8-94de-47890bb81a7d@z28g2000prd.googlegroups.com...
>>>> Hello -
>>>> I have the following query which is need of converting the Height,
>>>> Width, Depth data into a number. It is currently being stored as text
>>>> with extra spaces at the end in the database it comes from. Any
>>>> thoughts?
>>>> SELECT top 100 ltrim(rtrim(UPC)) as UPC, rtrim(HEIGHT) as H,rtrim
>>>> (WIDTH) as W,rtrim(DEPTH) as D
>>>> FROM brdatadb.dbo.Item
>>>> WHERE height >= '0'
>>>> ORDER BY upc
>>>> Thanks for any help!
>>>> Dan- Hide quoted text -
>>> - Show quoted text -
>> Darn, same error with your suggestion Conan.  :(- Hide quoted text -
>>
>> - Show quoted text -
>
> Oh...I gotcha now!
>
> FLOAT seems to be the winner here.  Thank you so much!
> Dan

> Dan wrote:
> > On Dec 11, 5:29 pm, "Conan Kelly"
> > <CTBarbarinNOS...@msnNOSPAM.comNOSPAM> wrote:
> >> Dan,
>
> >> In your reply to Tom's reply, you said your error was...
>
> >> Msg 245, Level 16, State 1, Line 1
> >> Conversion failed when converting the varchar value '6.5' to data type
> >> int
>
> >> Just guessing...maybe SQL Server doesn't like converting 6.5 (a
> >> decimal/float/real) to an integer.
>
> >> If you look at my original reply, I gave you 3 different examples:
>
> >> Height was converted to an integer,
> >> Width was converted to a float,
> >> Depth was converted to a decimal.
>
> >> Choose the most appropriate data type.
>
> >> Once again, I'm just guessing...I did not test this out.
>
> >> Experiment with the different data types to see which one will work for you.
>
> >> HTH,
>
> >> Conan
>
> >> "Dan" <dan.ga***@gmail.com> wrote in message
>
> >>news:d2a85ea0-6644-4f84-9dda-aeec1d0200e9@40g2000prx.googlegroups.com....
> >> On Dec 11, 5:07 pm, "Conan Kelly"
>
> >> <CTBarbarinNOS...@msnNOSPAM.comNOSPAM> wrote:
> >>> Dan,
> >>> You can use the CAST() function. There is also a CONVERT() function, but I
> >>> like CAST better than CONVERT. Look both of them up in Help/BOL.
> >>> What kind of number data type do you need?
> >>> /*** Your Original Script ***/
> >>> SELECT top 100
> >>> ltrim(rtrim(UPC)) as UPC
> >>> , rtrim(HEIGHT) as H
> >>> , rtrim(WIDTH) as W
> >>> , rtrim(DEPTH) as D
> >>> FROM
> >>> brdatadb.dbo.Item
> >>> WHERE
> >>> height >= '0'
> >>> ORDER BY
> >>> upc
> >>> Here are a some options:
> >>> SELECT top 100
> >>> ltrim(rtrim(UPC)) as UPC
> >>> , CAST(rtrim(HEIGHT) as int) as H
> >>> , CAST(rtrim(WIDTH) as float) as W
> >>> , CAST(rtrim(DEPTH) as decimal(5,2)) as D
> >>> Hope this helps,
> >>> Conan
> >>> "Dan" <dan.ga***@gmail.com> wrote in message
> >>>news:f555dc97-d4d2-4fc8-94de-47890bb81a7d@z28g2000prd.googlegroups.com....
> >>>> Hello -
> >>>> I have the following query which is need of converting the Height,
> >>>> Width, Depth data into a number. It is currently being stored as text
> >>>> with extra spaces at the end in the database it comes from. Any
> >>>> thoughts?
> >>>> SELECT top 100 ltrim(rtrim(UPC)) as UPC, rtrim(HEIGHT) as H,rtrim
> >>>> (WIDTH) as W,rtrim(DEPTH) as D
> >>>> FROM brdatadb.dbo.Item
> >>>> WHERE height >= '0'
> >>>> ORDER BY upc
> >>>> Thanks for any help!
> >>>> Dan- Hide quoted text -
> >>> - Show quoted text -
> >> Darn, same error with your suggestion Conan.  :(- Hide quoted text -
>
> >> - Show quoted text -
>
> > Oh...I gotcha now!
>
> > FLOAT seems to be the winner here.  Thank you so much!
> > Dan
>
> I would suggest that you be very careful with using float.  Float is
> going to give you an approximate number - and I am thinking that maybe
> you really don't want that.
>
> You will most likely be better off with either numeric or decimal.  Just
> make sure with either of those that you set the length and precision
> correctly (e.g. decimal(6,2) or numeric(6,2)).
>
> Lookup data types in Books Online for additional information.
>
> Jeff- Hide quoted text -
>
> - Show quoted text -