Post reply

help with substring and cast

  • July 30, 2008 at 9:12 am

    #190389

    I am trying to split a column, but it is an int. so I used cast to make it an varchar, but how can I get the yr and qtr in the select statement.

    SELECT CAST(yrqtr AS VARCHAR(6))as yrqtr

    from DwEnterprise.TimelyDischarge_Rpt

    I need to return these values:

    substring(yrqtr,1,4) as yr

    substring(yrqtr,5,2) as qtr

  • July 30, 2008 at 9:33 am

    #850618

    SELECT substring(CAST(yrqtr AS VARCHAR(6)),1,4) as yr,

    substring(CAST(yrqtr AS VARCHAR(6)),5,2) as qtr

    from DwEnterprise.TimelyDischarge_Rpt

  • July 30, 2008 at 9:44 am

    #850635

    Thanks,

    Now how do I change it back to int? Because in SSIS I'm doing a lookup transformation and the data types need to match?

  • July 30, 2008 at 9:49 am

    #850645

    SELECT cast(substring(CAST(yrqtr AS VARCHAR(6)),1,4) as int) as yr,

    cast(substring(CAST(yrqtr AS VARCHAR(6)),5,2) as int) as qtr

    from DwEnterprise.TimelyDischarge_Rpt

  • July 30, 2008 at 10:02 am

    #850652

    Thanks again steveb,

    I have another question? Could I shorten my code by using the "left" and "right" functions? Are there any performance or potential problems?

    select cast(left (yrqtr ,4) as int)as yr,

    cast(right (yrqtr ,1) as int)as qtr

    from DwEnterprise.TimelyDischarge_Rpt

    vs.

    SELECT cast(substring(CAST(yrqtr AS VARCHAR(6)),1,4) as int) as yr,

    cast(substring(CAST(yrqtr AS VARCHAR(6)),5,2) as int) as qtr

    from DwEnterprise.TimelyDischarge_Rpt

  • July 30, 2008 at 10:12 am

    #850658

    No worries,

    you could use the Left and Right functions.

    I am not aware of any performance difference between the two and couldn't find anything aobut it on BOL.

  • July 30, 2008 at 12:48 pm

    #850791

    There's no performance difference between the two.

    I think your sample with Left and Right may be missing the cast to varchar inside the Left and Right functions, from what you posted earlier.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • July 30, 2008 at 1:03 pm

    #850805

    Thanks for you help!

  • July 30, 2008 at 7:47 pm

    #850956

    steveb (7/30/2008)

    No worries,

    you could use the Left and Right functions.

    I am not aware of any performance difference between the two and couldn't find anything aobut it on BOL.

    LEFT and RIGHT do run a bit faster than SUBSTRING...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 4, 2008 at 7:44 am

    #852679

    I just ran this test:

    set statistics time on

    go

    declare @A char(1)

    select @A = left(col4, 1)

    from dbo.sometable

    select @A = substring(col4, 1, 1)

    from dbo.sometable

    go 100

    Execution time was identical for both commands.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • August 4, 2008 at 6:27 pm

    #852957

    GSquared (8/4/2008)

    I just ran this test:

    set statistics time on

    go

    declare @A char(1)

    select @A = left(col4, 1)

    from dbo.sometable

    select @A = substring(col4, 1, 1)

    from dbo.sometable

    go 100

    Execution time was identical for both commands.

    Ah, thanks Gus... and my apologies... I should have tested again before I said anything. I ran the following test years ago and got a difference. Might have just gotten lucky on the LEFT times because the same code in 2k or 2k5 on a quiet box shows the either can win by as much as 200ms on a million rows and sometimes it's a perfectly dead heat.

    DECLARE @BitBucket VARCHAR(80)

    SET STATISTICS TIME ON

    SELECT @BitBucket = LEFT(SomeCsv,10)

    FROM dbo.JBMTest

    WHERE LEFT(SomeCsv,10) = 'Part01,Par'

    SELECT @BitBucket = SUBSTRING(SomeCsv,1,10)

    FROM dbo.JBMTest

    WHERE SUBSTRING(SomeCsv,1,10) = 'Part01,Par'

    SET STATISTICS TIME OFF

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 5, 2008 at 8:09 am

    #853195

    I think it's likely that Left/Right use the same code as Substring, just with one less parameter. I know that's how I'd program them.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy