SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Spiltting columns in sql 2008


Spiltting columns in sql 2008

Author
Message
Trout
Trout
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 89
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
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7951 Visits: 25280
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

Before posting a performance problem please read
vivekkumar341
vivekkumar341
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 93
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
Trout
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 89
hello now got it working...
thanks i will review my code yours worked.
cheers
vivekkumar341
vivekkumar341
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 93
your welcome :-)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26478 Visits: 17557
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jason Selburg
Jason Selburg
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3729 Visits: 4110
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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7951 Visits: 25280
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

Before posting a performance problem please read
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7453 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search