Solved

Can I select only a portion of the text in a column within a stored procedure?

Posted on 2014-03-04
12
183 Views
Last Modified: 2014-03-04
Here's what I want to do:

I've got a recordset that I'm calling using a stored procedure. Rather than the entire "description" field, I want to be able to limit what's there to 75 characters and follow the last word with a "...".

I've done this before using something like this:

$position = '150';                    
        $message = $description;                    
        $post = substr($message,$position,1);                                        
        if($post !=" ") {                    
        $length = strlen( $message );                    
        while($post !=" " && $position < $length){                    
        $i =1;                    
        $position = $position+$i;                    
        $message =$description;                    
        $post = substr($message,$position,1);  

But I want to try to do the heavy lifting within the store procedure. Is that possible and, if so, how?

This is what my stored procedure looks like currently:

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, cer.description 
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


What do you think?
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
  • 6
  • 5
12 Comments
 
LVL 33

Expert Comment

by:Big Monty
ID: 39903814
You could probably do something like this:

in your select clause, for the description field, change it to:

description = case Len( cer.description ) when 78 then Left( cer.description, 75 ) + '...' else cer.description end
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39903881
In publishing this is called a "teaser fragment" and it's done this way in PHP.  I've never tried to do it in SQL.

<?php // demo/teaser_fragment.php
error_reporting(E_ALL);


// CREATE A TEASER FRAGMENT HEADLINE
// RETURN FIRST FEW WHOLE WORDS FOLLOWED BY ELLIPSES
// WITH A LINK TO THE FULL ARTICLE
// $length IS MINIMUM TRUNCATION CHARACTER COUNT


function teaser_fragment($text, $length=32, $url='#', $delim='|||')
{
    // IF TRUNCATION IS NEEDED
    if (strlen($text) > $length)
    {
        // IF TRUNCATION IS NEEDED, BREAK STRING APART
        $t = wordwrap($text, $length, $delim);
        $a = explode($delim, $t);
        $z = '...';
    }
    // IF TRUNCATION IS NOT NEEDED
    else
    {
        $a[0] = $text;
        $z = NULL;
    }

    // CONSTRUCT THE FRAGMENT WITH THE LINK AND ADD ELLIPSIS (LINK) TO THE END
    $teaser
    = '<a target="_blank" href="'
    . $url
    . '">'
    . $a[0]
    . $z
    . '</a>'
    ;
    return $teaser;
}



// USE CASES
echo "<pre>";
echo PHP_EOL;
echo "1...5...10...15...20...25...30...35...40...45..." . PHP_EOL;
echo teaser_fragment('Now is the time for all good men to come to the aid of their party');

echo PHP_EOL;
echo teaser_fragment('Now is the time for all good men to come to the aid of their party', 300);

echo PHP_EOL;
echo teaser_fragment('Now is the time for all good men to come to the aid of their party', 15, 'http://en.wikipedia.org/wiki/Filler_text');

Open in new window

0
 

Author Comment

by:brucegust
ID: 39903883
Big Monty, this looks great, but would you be willing to show me how your suggestion gets inserted into my current procedure?

Here's the entire code:

USE [BIProd]
GO
/****** Object:  StoredProcedure [dbo].[cer_ModelList]    Script Date: 3/4/2014 10:50:02 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[cer_ModelList]
    @UserID int
AS
SET NOCOUNT ON;

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, cer.description 
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


How do I incorporate your syntax?
0
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 
LVL 33

Expert Comment

by:Big Monty
ID: 39903891
change your select statement to:

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 Len( cer.description ) when 78 then Left( cer.description, 75 ) + '...' 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

0
 

Author Comment

by:brucegust
ID: 39903914
Sweet! One more thing...

In my HTML, I'm echoing your "description" like this: .$modellist[$i]["description"] with that query looking like...

if (!function_exists('cer_modellist')) {
    function cer_modellist($userid) {
        global $edb,$_SESSION;
        
        $data = array();
         $sql_data = "exec cer_ModelList $userid";
         if ($_SESSION["portaladmin"] == 1 && $_SESSION["showsql"] != "no")
            echo display_detail("cer_functions.php",$sql_data);
        $result_data = odbc_exec($edb, $sql_data);
        
        $count = 0;
        while (odbc_fetch_row($result_data))
            {
            $count++;
            $data[$count]["cermodelid"]= odbc_result($result_data, "cermodelid");
            $data[$count]["createuserid"] = odbc_result($result_data, "createuserid");
            $data[$count]["assetshortname"] = htmlspecialchars(odbc_result($result_data, "assetshortname"), ENT_QUOTES);
            $data[$count]["statusid"] = htmlspecialchars(odbc_result($result_data, "statusid"), ENT_QUOTES);
            $data[$count]["entityshortname"] = htmlspecialchars(odbc_result($result_data, "entityshortname"), ENT_QUOTES);
            $data[$count]["description"] = htmlspecialchars(odbc_result($result_data, "description"), ENT_QUOTES);
            $data[$count]["statusshortname"] = htmlspecialchars(odbc_result($result_data, "statusshortname"), ENT_QUOTES);
            $data[$count]["createdate"] = date('m/d/Y', strtotime(odbc_result($result_data, "createdate")));
            }
        $data["count"] = $count;
        return $data;
        }
    }

Open in new window


I'm still getting the entire text. What do I need to change in order to get your new and improved version. And I have successfully changed and saved your new stored procedure!
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 39903932
I'm not much of a PHP guy, so I can't really help you there...

Did you try running the SP directly in the database to see if the results are correct?
0
 

Author Comment

by:brucegust
ID: 39903969
Well, now that you mention it, while there were no errors, when I ran the query in the Management Studio, while I didn't get any errors, I didn't get an abbreviated description. What do I need to change?
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 39903977
make sure your string of characters is longer than 78 chars, if it isn't, it won't truncate
0
 

Author Comment

by:brucegust
ID: 39904034
I've got one description which is "I changed the varchar for this field to 500 rather than 250. Gives the user the chance to elaborate a bit more."

Just some nonsense to fill some space.

Then I went back into your query and changed 78 to 10 just so the exaggerated value might make it more obvious as to whether or not things were working right.

When I hit "Execute Query" I get the entire description field in every row.
0
 
LVL 33

Accepted Solution

by:
Big Monty earned 500 total points
ID: 39904048
sorry, i goofed, i had it EQUAL to 78, when it should be greater than:

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 ) >= 78 then Left( cer.description, 75 ) + '...' 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

0
 

Author Comment

by:brucegust
ID: 39904057
That did it and it's showing up great!

One last thing, and I'm going to go ahead and award you your points so I'm not trying to get more than my money's worth...

Is there a way to prevent words from being truncated? So instead of buil..., it would say building...

Let me know if you want me to open up another question, but I would be interested in knowing if there's a way to do it.
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 39904067
i'm sure there is a way to do it, you would have to search for a white space around the 75 char mark. I'm not sure what the syntax would be, but i'm sure someone else could assist you
0

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

751 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