Display only text/ characters in a string

Question

Post reply

Display only text/ characters in a string

sql_ques

SSCommitted

Points: 1546
More actions
April 5, 2012 at 5:00 am

#391952

Hi,
Is it possible to display only the text in the field.
Output:
'07-12-2011 15:57 [GMT +0:00] Some name: Client canot access the site'
I want to display only this: 'Some name: Client canot access the site'
Is it possible. How to do this?
Thanks

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

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

CoolCodeShare SSCrazy Points: 2584 More actions · Answer 1

could you please add more details? what is your query?

sql_ques SSCommitted Points: 1546 More actions · Answer 2

I am doing a select from the database for report generation

SELECT ID 'Job ID', date 'Call Date', Calldescription 'Issue', Calldetails 'Details' ,

CONVERT(CHAR(8), DATEADD(minute,totaltime,''),8) 'Time Taken',

status 'Call Status'

FROM TABLENAME

Also, using SSRS expression is it possible to display only rthe text and remove the date and time from the string?

Thank for your help

sql_ques SSCommitted Points: 1546 More actions · Answer 3

Output is:

2012-03-02 03:30:39.000

Creation of account

02-03-2012 03:31 [GMT +0:00] John Smith: Create user account

2012-03-13 06:30:00.000

Lowell SSC Guru Points: 323504 More actions · Answer 4

ok...help us help you;

what would you like to do to the data? for example, if you KNOW the datetime string is always at the front, and always has the GMT offset inside the square brackets, would you just want the string to the right of the ']'?

does the data ALWAYS have the timestamp stuff in it? does it vary in location, or can it appear more than once inside the string?

/*--results: ' Some name: Client canot access the site'*/

declare @ColumnPlaceHolder varchar(100)

SET @ColumnPlaceHolder ='07-12-2011 15:57 [GMT +0:00] Some name: Client canot access the site'

SELECT SUBSTRING(@ColumnPlaceHolder, --your column in the table

CHARINDEX(']',@ColumnPlaceHolder) + 1, --where the bracket starts, plus one more char

100) --the size of the field

--FROM YOURTABLE

EDIT

based on your one example, here's a way via substrings and charindex to chop it up into 3 peices

/*--results:

TheTime TheOffset TheMessage

07-12-2011 15:57 [GMT +0:00] Some name: Client canot access the site

*/

declare @ColumnPlaceHolder varchar(100)

SET @ColumnPlaceHolder ='07-12-2011 15:57 [GMT +0:00] Some name: Client canot access the site'

SELECT SUBSTRING(@ColumnPlaceHolder,1,CHARINDEX('[',@ColumnPlaceHolder) -1) As TheTime,

SUBSTRING(@ColumnPlaceHolder,CHARINDEX('[',@ColumnPlaceHolder ) ,(CHARINDEX(']',@ColumnPlaceHolder) - CHARINDEX('[',@ColumnPlaceHolder ) +1)) As TheOffset,

SUBSTRING(@ColumnPlaceHolder, --your column in the table

CHARINDEX(']',@ColumnPlaceHolder) + 1, --where the bracket starts, plus one more char

100) As TheMessage--the size of the field

--FROM YOURTABLE

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sql_ques SSCommitted Points: 1546 More actions · Answer 5

The timestamp always appears in the front, and I want to display the string after the ']'

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

Since you know it is ALWAYS there the post Lowell shared above should work just fine. You may have to tweak it slightly to get exactly what you want.

_______________________________________________________________

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/