Result should display only certain information in select command

Question

Result should display only certain information in select command

kshatriya24

SSC Eights!

Points: 887
More actions
May 23, 2013 at 10:46 am

#394839

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

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

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

kshatriya24 (5/23/2013)
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

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/

kshatriya24 SSC Eights! Points: 887 More actions · Answer 2

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.

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 3

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)

kshatriya24 SSC Eights! Points: 887 More actions · Answer 4

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

kshatriya24 SSC Eights! Points: 887 More actions · Answer 5

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

ErrLog Ten Centuries Points: 1040 More actions · Answer 6

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>>'

kshatriya24 SSC Eights! Points: 887 More actions · Answer 7

kshatriya24

SSC Eights!

Points: 887

May 23, 2013 at 12:37 pm

#1617997

Thank you. It worked