Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Spiltting columns in sql 2008 Expand / Collapse
Author
Message
Posted Tuesday, October 2, 2012 8:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 3, 2012 6:15 PM
Points: 38, 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
Post #1367378
Posted Tuesday, October 2, 2012 8:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Post #1367380
Posted Tuesday, October 2, 2012 8:52 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 18, 2013 1:47 AM
Points: 196, Visits: 87
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
Post #1367383
Posted Tuesday, October 2, 2012 8:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 3, 2012 6:15 PM
Points: 38, Visits: 89
hello now got it working...
thanks i will review my code yours worked.
cheers
Post #1367384
Posted Tuesday, October 2, 2012 10:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 18, 2013 1:47 AM
Points: 196, Visits: 87
your welcome
Post #1367392
Posted Tuesday, October 2, 2012 10:44 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 1,947, Visits: 3,176
>> 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.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1367396
Posted Wednesday, October 3, 2012 9:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 13,326, Visits: 12,813
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 Moden's 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)
Post #1367805
Posted Wednesday, October 3, 2012 9:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:11 PM
Points: 2,717, Visits: 3,857
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
Post #1367815
Posted Wednesday, October 3, 2012 10:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Post #1367830
Posted Wednesday, October 3, 2012 7:37 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 3,438, Visits: 5,390
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!
Post #1368135
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse