May 23, 2013 at 10:46 am
I am new to SQL enviornment. I have question regarding using select command. Current SQL command
Select caseid, casetype, Casesubtype and casedescription field from table. The report comes out in the following format (e.g) .
caseid Casetype casesubtype casedescription
1234586 customercomplaint Billing Customer called to complaint they called at 2.00 PM , they had to leave the message on answering machine
4578555 Billinginfo water call came at 10.00 PM to pay the bill
7854555 Billininfo water1 customer called to zxcvbbbbbbbbbbbbbb
End user requested they would like to see only selected information in casedescription field like 2.00PM, 10.00 PM if time is not enter then provide all the information
caseid Casetype casesubtype casedescription
1234586 customercomplaint Billing 2.00 PM
4578555 Billinginfo water 10.00 PM
7854555 Billininfo water1 customer called to zxcvbbbbbbbbbbbbbb
casedescription field in free form field. (1000 charchater)
please advise.
Thank you
May 23, 2013 at 10:52 am
kshatriya24 (5/23/2013)
I am new to SQL enviornment. I have question regarding using select command. Current SQL commandSelect caseid, casetype, Casesubtype and casedescription field from table. The report comes out in the following format (e.g) .
caseid Casetype casesubtype casedescription
1234586 customercomplaint Billing Customer called to complaint they called at 2.00 PM , they had to leave the message on answering machine
4578555 Billinginfo water call came at 10.00 PM to pay the bill
7854555 Billininfo water1 customer called to zxcvbbbbbbbbbbbbbb
End user requested they would like to see only selected information in casedescription field like 2.00PM, 10.00 PM if time is not enter then provide all the information
caseid Casetype casesubtype casedescription
1234586 customercomplaint Billing 2.00 PM
4578555 Billinginfo water 10.00 PM
7854555 Billininfo water1 customer called to zxcvbbbbbbbbbbbbbb
casedescription field in free form field. (1000 charchater)
please advise.
Thank you
There really is no concrete way to do this. What about if they enter the time as 2:00 or even 02:00. Even your two examples would be really difficult. One has a space the other one doesn't. No matter what kind of elaborate rules you put in place there will always be something that doesn't work right. Consider something like 2-2:30 P.M. or 4:23am.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 23, 2013 at 11:00 am
We are getting data from website like
'Wrong day The incident occurred date: 4/18/2012 The incident occurred time: 6:45 AM This incident report is for residential'
Wrong day watering wrong time of day The incident occurred date: 04/18/12 The incident occurred time: 1:43 PM This incident report is for residential
instead of the entire information , end user just like to see "6.45 AM" in case description column.
May 23, 2013 at 11:27 am
You try like this
declare @variable varchar(max)='water call came at 10.00 PM to pay the bill'
select substring(@variable,PATINDEX('%[0-9]%',@variable),8)
GO
declare @variable varchar(max)='water 4.45 PM at to pay the bill'
select substring(@variable,PATINDEX('%[0-9]%',@variable),8)
go
declare @variable varchar(max)='water call 24/12/35 came at 10.00 PM to pay the bill'
select substring(@variable,PATINDEX('%[0-9][.,:]%',@variable)-1,8)
go
declare @variable varchar(max)='water call 24/12/35 came at 10:00 PM to pay the bill'
select substring(@variable,PATINDEX('%[0-9][.,:]%',@variable)-1,8)
this is not exact solution .it s like hint using patindex you can try
Malleswarareddy
I.T.Analyst
MCITP(70-451)
May 23, 2013 at 11:57 am
Thank you Maleswara
it worked
CASE
WHEN DESCRLONG LIKE '%The incident occurred time%' THEN substring(DESCRLONG,PATINDEX('%The incident occurred time:%',DESCRLONG)+28,8)
ELSE
CAST(DESCRLONG AS VARCHAR(1000))
Thank you for your help.
apperciate
May 23, 2013 at 12:21 pm
Hi
how do i add column name at the end
DESCRLONG as 'Case Description' ??? currenlty it display the information as 'No column Name'
CASE
WHEN DESCRLONG LIKE '%The incident occurred time%' THEN substring(DESCRLONG,PATINDEX('%The incident occurred time:%',DESCRLONG)+28,8)
ELSE
CAST(DESCRLONG AS VARCHAR(1000))
May 23, 2013 at 12:29 pm
CASE
WHEN DESCRLONG LIKE '%The incident occurred time%' THEN substring(DESCRLONG,PATINDEX('%The incident occurred time:%',DESCRLONG)+28,8)
ELSE
CAST(DESCRLONG AS VARCHAR(1000))
END AS '<<Casedescription>>'
May 23, 2013 at 12:37 pm
Thank you. It worked
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply