Separating data in table

Question

Separating data in table

Imke Cronje

SSCarpal Tunnel

Points: 4826
More actions
July 5, 2007 at 7:47 am

#69839

Hi Guys,
I have a query that looks like this:
Select servername,LEFT (alert, CHARINDEX(' on ', alert) ) as Alert
from tempTNG_Alerts
It takes the data from one column and only displays the data that before the 'on' word.
The data in table looks like this:
Test Alert Name on Feb 7 2007 13:16PM
The Query mentioned above takes away the date and only displays the data before the 'on' clause. How can I display the data after the on clause. I can't substring it because not every date in the table starts at the same position.
Kind Regards
IC

Viewing 4 posts - 1 through 3 (of 3 total)

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

Gordon Pollokoff SSCommitted Points: 1930 More actions · Answer 1

Select   servername,
LEFT (alert, CHARINDEX(' on ', alert) ) as Alert,
substring(alert,CHARINDEX(' on ', alert) +4,len(alert) - (CHARINDEX(' on ', alert)+3)) as AlertDate
from tempTNG_Alerts

Gordon Pollokoff

"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones

Imke Cronje SSCarpal Tunnel Points: 4826 More actions · Answer 2

Thanks but I found this:

Select servername,LEFT (alert, CHARINDEX(' on ', alert) ) as Alert,

LTRIM(RIGHT(alert, CHARINDEX(' on ', REVERSE(Alert)) + 20)) as AlertDate

from tempTNG_Alerts

Gordon Pollokoff SSCommitted Points: 1930 More actions · Answer 3

That will work with the given example. However, it is dependent on the length of the data after ' on ' begin constant. Should the format of your alert string change, you would need to modify the statement.

Gordon Pollokoff

"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones