Solved

How can I search for whitespace in my stored procedure?

Posted on 2014-03-04
9
135 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:ScottPletcher
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
 
LVL 69

Expert Comment

by:ScottPletcher
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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:
ScottPletcher 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:ScottPletcher
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now