Post reply

TSQL

  • September 11, 2008 at 4:00 am

    #212556

    Hi,

    Can any one help me please?

    I need to take the text between first two dates for every record. ..the date format entered by user may be any format..

    For Example:

    I have a record as

    25/08/2008 Karin.Kapaun:ne 13/06/2008 Elisabeth.Hager: Karin: möchte erst 2010 reisen

    The Output should be

    Karin.Kapaun:ne

    Thanks

    anitha

  • September 11, 2008 at 4:22 am

    #870190

    If there are always spaces after the first date and before the second date (and no spaces inbetween) you should be able to do something like:

    SELECT SUBSTRING(myColumn, CHARINDEX(' ', myColumn, 1) + 1, CHARINDEX(' ', myColumn, CHARINDEX(' ', myColumn) + 1) - CHARINDEX(' ', myColumn))

    FROM myTable

    Basically this code looks for the first space and then the second and substrings the difference between the two.

    HTH,


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • September 11, 2008 at 4:42 am

    #870202

    Hi Adrian

    Thanks for the query.

    Can you please tell me how to modify the query if there is no space between the data and text.. since some of the records don't have spaces between?

    Thanks,

    Anitha

  • September 11, 2008 at 4:45 am

    #870204

    Before I can do that you've said that the user may enter any date format, do you mean that it can be anything like dd/mm/yyyy, dd-mm-yyyy etc. Can you give some more examples of the date formats they use?


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • September 11, 2008 at 4:54 am

    #870212

    Hi Adrian,

    Sorry to not mentioning the date format before.

    The different formats present in table are

    11.9.

    11/9

    11/9/

    11.9.08

    11/09/2008

    Thanks in advance

    Anitha

  • September 11, 2008 at 5:04 am

    #870219

    Okay, no problem.

    This SUBSTRING statement looks for the first alphabetic (a to z) character for a starting point; and the first alphabetic character (or space) followed by a numeric (assumed to be the start of the second date string.

    SELECT myColumn,

    SUBSTRING (myColumn, PATINDEX('%[A-Za-z]%', myColumn), PATINDEX('%[ A-Z][0-9]%', myCOlumn) - PATINDEX('%[A-Za-z]%', myColumn))

    FROM myTable


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • September 11, 2008 at 5:49 am

    #870235

    I am not sure.

    But from Regular Expression in sql server 2005

    U can archive that task.:)

Viewing 7 posts - 1 through 6 (of 6 total)

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