October 1, 2010 at 11:45 am
hey guys, i have heard a lot of good things about this forum and i am new to it. I have a question about extracting parts of columns
here is the table info
create table #test
(
id int identity(1,1),
name varchar(1000)
)
insert into #test
values( 'Live and Let Live volume 2 (3rd 2/14)' )
insert into #test
values( 'Live and Let Live (the sequel) volume 5 (5th 12/16)' )
insert into #test
values( 'Die another day part 2 1st 4/23)' )
insert into #test
values( 'Die another day part 6 (latest) (5th 1/18)' )
select * from #test
Question:
How can i get extract the date part from the 'name' column in this table? The date part is the last digits in the column with the '/' seperating the month from the day
for example for id = 1, the date column is 2/14 (february 14th).
for id = 2, the date column is 12/16 (december 16th). etc
How can i get just that tiny part of the whole column for all the columns in the table?
October 1, 2010 at 12:44 pm
it's kind of hard, but you have to make some assumptions based on the sample data.
i'm assuming:
there are TWO chars after the slash to capture.
there is always a space before the date starts.
i added an example in case the title has a slash in it, ie 'Superman/Clark Kent Must Die (latest) (5th 12/18)'
i left a lot of intermediate manipulations so you can see step by step what i was trying to do.
create table #test
(
id int identity(1,1),
name varchar(1000)
)
insert into #test
values( 'Live and Let Live volume 2 (3rd 2/14)' )
insert into #test
values( 'Live and Let Live (the sequel) volume 5 (5th 12/16)' )
insert into #test
values( 'Die another day part 2 1st 4/23)' )
insert into #test
values( 'Die another day part 6 (latest) (5th 1/18)' )
insert into #test
values( 'Superman/Clark Kent Must Die (latest) (5th 12/18)' )
select * from #test
/*--results
Reversed TheSlashindex TheSpaceIndex TheSubString TheFinalResult
)41/2 dr3( 2 emulov eviL teL dna eviL 4 6 41/2 2/14
)61/21 ht5( 5 emulov )leuqes eht( eviL teL dna eviL 4 7 61/21 12/16
)32/4 ts1 2 trap yad rehtona eiD 4 6 32/4 4/23
)81/1 ht5( )tsetal( 6 trap yad rehtona eiD 4 6 81/1 1/18
)81/21 ht5( )tsetal( eiD tsuM tneK kralC/namrepuS 4 7 81/21 12/18
*/
SELECT
--for reference
REVERSE(name)As Reversed,
--get the LAST slash in the name, in case a slash appears in the title.
CHARINDEX('/',REVERSE(name)) As TheSlashindex,
-- find the space before the charindex
CHARINDEX(' ',REVERSE(name)) as TheSpaceIndex,
--find the substring between those two charindexes
SUBSTRING(REVERSE(name),CHARINDEX('/',REVERSE(name)) -2 ,CHARINDEX(' ',REVERSE(name)) - (CHARINDEX('/',REVERSE(name)) -2)) As TheSubString,
--reverse that substring to get the results
REVERSE(SUBSTRING(REVERSE(name),CHARINDEX('/',REVERSE(name)) -2 ,CHARINDEX(' ',REVERSE(name)) - (CHARINDEX('/',REVERSE(name)) -2)) ) As TheFinalResult
from #test
Lowell
October 1, 2010 at 2:44 pm
this is exactly what i wanted thank you very much. i made a slight mistake in the question however.
TheFinalResult column is actually month/year not month/day.
One last question would be how to convert a string like 2/11 to datetime month/year i.e. february 2011
October 1, 2010 at 3:57 pm
i am trying to achieve this by maybe seperating both numbers into two columns, i came up with this script to display all the records on the left of the '/' and the right of the '/' so i can seperate the month from the year. After this I plan on converting them to datetime.
what am i doing wrong here?
select thefinalresult, left(thefinalresult, charindex(thefinalresult, '/',2))'Month', right(thefinalresult, charindex(thefinalresult, '/',2))'Year' from #test1
This is still using the ddl above.
October 1, 2010 at 4:10 pm
gaheinze (10/1/2010)
i am trying to achieve this by maybe seperating both numbers into two columns, i came up with this script to display all the records on the left of the '/' and the right of the '/' so i can seperate the month from the year. After this I plan on converting them to datetime.what am i doing wrong here?
select thefinalresult, left(thefinalresult, charindex(thefinalresult, '/',2))'Month', right(thefinalresult, charindex(thefinalresult, '/',2))'Year' from #test1
This is still using the ddl above.
The right portion: right(thefinalresult, charindex(thefinalresult, '/',2)
Change to: right(thefinalresult, len(thefinalresult) - charindex(thefinalresult, '/',2)
You need to deal with the charindex still being left based.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 4, 2010 at 9:36 am
still not getting the desired output.
October 4, 2010 at 9:52 am
What are you getting?
We are happy to help, but please do some testing and explain or ask about what specifically you don't understand. What about the results don't make sense?
With regards to converting 2/11 to a date, I typically convert to the first day of the month.
select cast(@month + '/01/' + @year) as datetime
I would separate out the results you get from your strings into a month and year variable
October 4, 2010 at 10:46 am
another option is to use STUFF to insert 01/20 into the middle of 02/11 to produce 02/01/2011
here's a simple example :
/*--Results:
2/01/2014
12/01/2016
4/01/2023
1/01/2018
12/01/2018
*/
SELECT STUFF(TheFinalResult,CHARINDEX('/',TheFinalResult) + 1,0,'01/20') As TheFinalDate
FROM (
SELECT '2/14' As TheFinalResult UNION ALL
SELECT '12/16' UNION ALL
SELECT '4/23' UNION ALL
SELECT '1/18' UNION ALL
SELECT '12/18') X
Lowell
October 4, 2010 at 5:43 pm
Thanks for all your help guys, I will be more clear moving forward.
I have been working on a way to separate the month from the year and this is what i have so far with table structures
create table #test(
monthyear varchar(10)
)
go
insert into #test(monthyear)
select '6/11' union all
select '1/11' union all
select '12/10' union all
select '10/10'
SELECT
monthyear,
SUBSTRING(monthyear, 0, CHARINDEX('/', monthyear, 2))'month',
SUBSTRING(monthyear, 3, CHARINDEX('/', reverse(monthyear), 0))'year'
FROM #test
the last 2 (12/10 and 10/10) don't quite format out correctly, is there something im missing?
October 4, 2010 at 10:06 pm
Using a variation on Lowell's idea, and assuming your dates are US format:
SELECTinput = T.monthyear,
the_month = MONTH(CA.the_date),
the_year = YEAR(CA.the_date)
FROM#test T
CROSS
APPLY(
SELECTCONVERT(DATE, REPLACE(T.monthyear, '/', '/01/20'), 101)
)
CA (the_date);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 5, 2010 at 11:10 am
Thanks Lowell, Paul and Steve, the solutions are brilliant. Definitely learnt a lot. YOu are all appreciated.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply