Spiltting columns in sql 2008

Question

Post reply

Spiltting columns in sql 2008

Trout

SSC Eights!

Points: 814
More actions
October 2, 2012 at 8:14 pm

#267564

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

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

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]

vivekkumar341 Say Hey Kid Points: 681 More actions · Answer 2

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

Trout SSC Eights! Points: 814 More actions · Answer 3

hello now got it working...

thanks i will review my code yours worked.

cheers

vivekkumar341 Say Hey Kid Points: 681 More actions · Answer 4

vivekkumar341

Say Hey Kid

Points: 681

October 2, 2012 at 10:09 pm

#1544677

your welcome 🙂

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

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/

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 6

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 7

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]

Dwain Camps SSC Guru Points: 86978 More actions · Answer 8

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 9

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/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

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

vivekkumar341 Say Hey Kid Points: 681 More actions · Answer 11

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 🙂

kapil_kk SSC-Insane Points: 21316 More actions · Answer 12

:-):-P

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