Solved

How can I search for whitespace in my stored procedure?

Posted on 2014-03-04
9
147 Views
Last Modified: 2014-03-05
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?
0
Comment
Question by:brucegust
  • 4
  • 4
9 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 39904256
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
0
 

Author Comment

by:brucegust
ID: 39904471
Hey, lcohan!

It's still giving me the same thing as far as truncated words.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39904694
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
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39904712
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
0
 

Author Comment

by:brucegust
ID: 39904775
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?
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39905491
...
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
...
0
 

Author Comment

by:brucegust
ID: 39906581
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!
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39907084
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.
0
 

Author Comment

by:brucegust
ID: 39907151
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?
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question