October 25, 2012 at 10:35 am
I'm creating a report that shows all the results from a survey. It's a pretty easy report since all the results are in one table. However, when a customer selects NO and adds details why they werent satisfied, I get data like this:
New answers of If no, please advise how we could improve our service: [ID:58] are NOT HAPPY.
This data I cannot change, so I'm trying to do this on the report. I'm trying to only get the NOT HAPPY and beyond part.
The number of characters starting from N of NEW to E of ARE are 81 characters. What can I do to remove this part of the value:
New answers of If no, please advise how we could improve our service: [ID:58] are
Thanks!
October 25, 2012 at 10:51 am
I tried this and it worked but up to a limit.
Case When Len(ChangeLog) >= 5 Then Right(ChangeLog, Len(ChangeLog)-48) Else ChangeLog End As Reason
I can only go up to 48 characters.
Does anyone else have an idea?
Thanks
October 25, 2012 at 10:52 am
Start from the right:
right(fieldname, 10)
If you want to drop the '.' as well:
left(right(fieldname, 10), 9)
Works in vb and SQL.
October 25, 2012 at 11:22 am
After a little research, I found this worked for me.
SubString(ChangeLog, 82, Len(ChangeLog) - 40) as Reason
October 25, 2012 at 12:42 pm
Is the ID 58 going to be static in all of your data? If not, then the end-position is variable.
If it is static, then Stuff() will easily do what you need.
SELECT STUFF('New answers of If no, please advise how we could improve our service: [ID:58] are NOT HAPPY.', 1, 81, '')
The first parameter is the string (you can use a column name if you have a From clause), the second is the start position, the third is the number of characters you want to replace, and the last is what you want to replace them with.
So: SELECT STUFF('Bob', 2, 1, 'i')
will give "Bib". Start with "Bob", go to starting position 2 ("o"), replace 1 character, replace it with "i".
If the "replace it with" string is empty, then it just chops characters out of the string wherever you told it to.
The number of characters in the "replace with" string doesn't have to be the same as the number of characters you want to replace:
SELECT STUFF('Food', 2, 2, 'a')
= "Fad" (replaces two characters with one)
If you do need it to be dynamic, like "58" could be "1" or "10715" instead of "58", sometimes, then this might work:
DECLARE @String VARCHAR(1000) = 'New answers of If no, please advise how we could improve our service: [ID:1111] are NOT HAPPY.';
SELECT CHARINDEX(']', @String, 1)+5; -- Just here to show what it's doing to figure out where to stop replacing.
SELECT STUFF(@String, 1, CHARINDEX(']', @String, 1)+5, '')
All of that is T-SQL, and I don't know if you can do that in your data source. If not, SSRS has some comparable string-manipulation functions that can be called in the report.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply