TSQL

Question

TSQL

anithagovindarajalu

SSC Eights!

Points: 981
More actions
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

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

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

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

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]

anithagovindarajalu SSC Eights! Points: 981 More actions · Answer 2

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

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 3

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]

anithagovindarajalu SSC Eights! Points: 981 More actions · Answer 4

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

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 5

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]

dhaval.itprofessional Grasshopper Points: 18 More actions · Answer 6

I am not sure.

But from Regular Expression in sql server 2005

U can archive that task.:)