Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How can I search for whitespace in my stored procedure?

Posted on 2014-03-04
9
Medium Priority
?
160 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 70

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 70

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 70

Accepted Solution

by:
Scott Pletcher earned 2000 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 70

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

705 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