substring or charindex?

  • 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?

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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

  • 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.

  • 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.


    - Craig Farrell

    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

  • still not getting the desired output.

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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?

  • 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);

  • 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