How can I search for whitespace in my stored procedure?

Bruce Gust
Bruce Gust used Ask the Experts™
on
Here's my current stored procedure:

SELECT  at.shortname as assetshortname, en.shortname as entityshortname, st.shortname as statusshortname, cer.cermodelid, cer.assettypeid, 
cer.statusid, cer.createuserid, cer.entityid, cer.createdate, 
description = case when Len( cer.description ) >= 25 then Left( cer.description, 25 ) + '...' else cer.description end
from cer_Model cer 
inner join fin_AssetType at on at.assettypeid = cer.assettypeid
inner join ptl_Entity en on en.entityid = cer.entityid
inner join ptl_Status st on st.statusid = cer.statusid
where cer.createuserid = @UserID 
order by cer.createdate 

Open in new window


Works great with one exception: Instead of "This is a great stored procedure..." I'm getting "This is a great stored proce..."

How can incorporate the search and accommodation of whitespace so the end result is a complete word and not something that's been truncated?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
Try this:

SELECT  at.shortname as assetshortname, en.shortname as entityshortname, st.shortname as statusshortname, cer.cermodelid, cer.assettypeid,
cer.statusid, cer.createuserid, cer.entityid, cer.createdate,
description = case when Len(LTRIM(rtrim( cer.description ))) >= 25 then Left( cer.description, 25 ) + '...' else cer.description end
from cer_Model cer
inner join fin_AssetType at on at.assettypeid = cer.assettypeid
inner join ptl_Entity en on en.entityid = cer.entityid
inner join ptl_Status st on st.statusid = cer.statusid
where cer.createuserid = @UserID
order by cer.createdate
Bruce GustPHP Developer

Author

Commented:
Hey, lcohan!

It's still giving me the same thing as far as truncated words.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Easy enough to do, but are you sure you really want to if the last token/"word" is, say, 3+(4+?) chars?  I can see that only 1 or 2 (or 3) chars might be more annoying than helpful, but 3/4+ should be clearer than stripping it off.

At any rate, this should strip to the last space (literally, right now, it checks only for a space) before the 25 character limit:

case when Len( cer.description ) >= 25 then Left( cer.description, 25 - charindex(' ', reverse(Left( cer.description, 25)))) + '...' else cer.description end

For example:

select
description,
case when Len( cer.description ) >= 25 then Left( cer.description, 25 ) + '...' else cer.description end as original,
case when Len( cer.description ) >= 25 then Left( cer.description, 25 - charindex(' ', reverse(Left( cer.description, 25)))) + '...' else cer.description end as new
from (
    select 'This is a great stored procedure' as description
) as cer
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
As I think about it, maybe we should check for non alphanumeric -- anything not 0-9 nor a-z -- as that might be better.

For example:



select
description,
case when Len( cer.description ) >= 25 then Left( cer.description, 25 ) + '...' else cer.description end as original,
case when Len( cer.description ) >= 25 then Left( cer.description, 25 - charindex(' ', reverse(Left( cer.description, 25)))) + '...' else cer.description end as new_space_only,
case when Len( cer.description ) >= 25 then Left( cer.description, 25 - patindex('%[^0-9a-z]%', reverse(Left( cer.description, 25)))) + '...' else cer.description end as new_patindex
from (
    select 'This is a great stored procedure' as description union all
    select 'this shows how/patindex/changes/result'
) as cer
Bruce GustPHP Developer

Author

Commented:
Scott, I'm digging the pat/index route. How would I implement your suggestion into my original code? Your syntax works great as displaying the different options, but how do I get that goodness into my query?
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
...
description = case when Len( cer.description ) >= 25 then Left( cer.description, 25 - patindex('%[^0-9a-z]%', reverse(Left( cer.description, 25)))) + '...' else cer.description end
...
Bruce GustPHP Developer

Author

Commented:
Scott...awesome!

I'm going to award you the points, but I want to try and explain back to you WHY your suggestion worked and not just copy and paste your code without taking advantage of the opportunity to learn.

Line by line, here we go...

"description" - similar to the way I might code a select statement as "select cer.description as description."

"case" - SQL syntax similar to a switch statement in PHP. The "end" being the same kind of thing.

"LEN" - stands for length. At this point, I'm getting the length of the string.

LEFT - now I'm getting into some thick grass and I'm not sure what I'm doing. LEFT, according to the definition I found is "Returns the left part of a character string with the specified number of characters." Not sure how that relates to the magic you pulled off.

PATINDEX - literally the "pattern" of the string you're considering.

REVERSE - I saw this on the Microsoft site at http://technet.microsoft.com/en-us/library/ms180040.aspx. If I select a name like Robert and introduce the REVERSE dynamic, I get treboR. Impressive, but how do you use that in a way that accomplished what you did?

If you could offer a little explanation, I would really appreciate it. One thing I really dig about EE is the way in which the "experts" are also " teachers," so if you have the time, I would appreciate it.

Your rock, Scott! It's just that simple!
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
OK, let's review this.

It's easier to visualize it by looking at the different strings:
123456789!123456789@12345
This is a great stored procedure --full string
This is a great stored pr --25 bytes
rp derots taerg a si sihT --reverse of 25 bytes

Open in new window

To get 25 chars while avoiding partial words, we must go to byte 25, then "back up" until a "bad" byte / space / non-alphanumeric char is found.  Sadly, SQL can't "back up" or find the "last" of something in a string.

But, by reversing the string, we can instead look for the *first* bad byte.  And SQL can easily find the first match using PATINDEX/CHARINDEX.  Better yet, the location of the bad byte is how many bytes to subtract from 25 to get the final string.

That is, since the first chars in the reversed string will be what's at the end of the original string, the first work-break (bad) char there identifies the number of left-over bytes that we want to exclude from the final result.  

Thus, since space is in byte 3 in the reversed string, we subtract 3 from 25 to get 22, and that's how many bytes we need to pull from the original string to avoid getting any "leftover" chars at the end of that string.
Bruce GustPHP Developer

Author

Commented:
So the PATINDEX, in this case, is just the "pattern" of alpha-numeric characters. When you reverse the string, you're now starting at the end and checking to see that the pattern is in place until WHOOPS, I've got a space, which is not part of the pattern.

So, I count how many characters I had to go through before I hit that glitch and subtract that from my original 25 and BOOM, there's my camera ready string.

Yes?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial