Spiltting columns in sql 2008

  • hello i have one column that I need split into two, the colum records age value, and the name of the colum is ageatdeath

    results for eg are:

    51years

    62years

    45years

    there is no space

    so my script to separte into two columns on the table (death)

    is

    SELECT left(ageatdeath,(len(ageatdeath)-5) as [AGE_VAL], left(ageatdeath,2) AS [AGE_Units] FROM DEATH

    BUT EACH TIME IF RUN THIS SELECT STATEMENT I GET

    Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'as'.

    AND ITS ERROR NOTATION COMES AFTER

    SELECT left(ageatdeath,(len(ageatdeath)-5) as [AGE_VAL], as

    any suggestions on where I have gone wrong here.

    Regards Trout

  • No problrem with

    SELECT left(ageatdeath,(len(ageatdeath)-5)) as [AGE_VAL], left(ageatdeath,2) AS [AGE_Units] FROM #DEATH

    Yours:

    SELECT left(ageatdeath,(len(ageatdeath)-5) as [AGE_VAL], left(ageatdeath,2) AS [AGE_Units] FROM DEATH

    (len(ageatdeath)-5) -- yours

    (len(ageatdeath)-5)) -- correct code

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Hi Trout,

    I am suggesting some code please correct me if i am wrong.

    the column ageatdeath will always contain values like eg:

    51years

    62years

    45years

    suppose the age is "100years" then the below mention query will fail.

    "SELECT left(ageatdeath,(len(ageatdeath)-5) as [AGE_VAL], left(ageatdeath,2) AS [AGE_Units] FROM DEATH"

    but if you use the below mentioned code then it may not.

    SELECT SUBSTRING(ageatdeath,0,CHARINDEX('Y',ageatdeath,0)) AS AGE_VAL,

    SUBSTRING(ageatdeath,CHARINDEX('Y',ageatdeath,0),LEN(ageatdeath)) AS AGE_UNIT

    Please let me know if it is correct.:-)

    Thanks & Regards

    Vivek Kumar

  • hello now got it working...

    thanks i will review my code yours worked.

    cheers

  • your welcome πŸ™‚

  • CELKO (10/2/2012)


    >> I have one column that I need split into two, the column holds age value, and the name of the column is age_at_death <<

    TOTALLY WRONG! The age_at_death is a computed value define as

    (death_date – birth_date). Oh, and thanks for the rudeness of no DDL.

    >> There is no space <<

    Of course not! That would mean they you are formatting display data in the query in TOTAL VIOLATION OF A TIERED ARCHITECTURE!! Thisdis a computation:

    DATEDIFF (YY, death_date, birth_date) AS life_span

    >> any suggestions .. <<

    1. Kill the moron that did this and replace all his code. Seriously, a bad programmer will have poisoned so much code, you need to clean it.

    2. Get a minimal education on RDBMS principles. Do you want to be this stupid bastard when you grow up?

    3. Stop programming until you do. You will only hurt people now.

    Good grief Joe, did you forget to take your meds this morning? You complain about the rudeness of the OP, look at your response. Talk about rude!!!

    To the OP: Joe is absolutely correct that this age should be calculated. Storing calculated data is painful to maintain. There are some cases when this "absolute" rule can be broken. Age at death seems like one of those to me. It isn't like the value is ever going to change. πŸ˜‰

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Good grief Joe, did you forget to take your meds this morning? You complain about the rudeness of the OP, look at your response. Talk about rude!!!

    +1

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • Jason Selburg (10/3/2012)


    Good grief Joe, did you forget to take your meds this morning? You complain about the rudeness of the OP, look at your response. Talk about rude!!!

    +1

    +1

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Here's another way. Comments in the code tell you the right way to do it.

    CREATE TABLE #Persons

    (Name VARCHAR(20), Birth_Date DATE, Death_Date DATE

    -- Use a computed column if you need the text string (n)nnyears

    ,AgeAtDeath AS (CAST(DATEDIFF(year, Birth_Date, Death_Date) AS VARCHAR(3)) + 'years'))

    INSERT INTO #Persons

    SELECT 'Grumpy', DATEADD(year, -51, '2012-05-10') - ABS(CHECKSUM(NEWID())) % 100, '2012-05-10'

    UNION ALL SELECT 'Sleepy', DATEADD(year, -62, '2012-04-23') - ABS(CHECKSUM(NEWID())) % 100, '2012-04-23'

    UNION ALL SELECT 'Dopey', DATEADD(year, -45, '2012-03-15') - ABS(CHECKSUM(NEWID())) % 100, '2012-03-15'

    SELECT Name, Birth_Date, Death_Date, AgeAtDeath

    -- Parse the text string like this

    ,AGE=LEFT(AgeAtDeath, PATINDEX('%[^0-9]%', AgeAtDeath)-1)

    -- Or just do it the right way

    ,AGE=DATEDIFF(year, Birth_Date, Death_Date)

    FROM #Persons

    DROP TABLE #Persons

    The set up data you can ignore. It was just for my amusement.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Hi vivek,

    SELECT SUBSTRING(ageatdeath,0,CHARINDEX('Y',ageatdeath,0)) AS AGE_VAL,

    SUBSTRING(ageatdeath,CHARINDEX('Y',ageatdeath,0),LEN(ageatdeath)) AS AGE_UNIT

    this code also works fine.

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Trout (10/2/2012)


    hello i have one column that I need split into two, the colum records age value, and the name of the colum is ageatdeath

    results for eg are:

    51years

    62years

    45years...

    What about infants? Is it always 'years' or does your data include 'months' or 'days'?

    β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • kapil190588 (10/5/2012)


    Hi vivek,

    SELECT SUBSTRING(ageatdeath,0,CHARINDEX('Y',ageatdeath,0)) AS AGE_VAL,

    SUBSTRING(ageatdeath,CHARINDEX('Y',ageatdeath,0),LEN(ageatdeath)) AS AGE_UNIT

    this code also works fine.

    Your welcome dude πŸ™‚

  • :-):-P

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

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