Solved

How can I search for whitespace in my stored procedure?

Posted on 2014-03-04
9
148 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 40

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

685 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