Substring_Index

[TamedTech]

Hello guys,

Many months ago the young Rudy gave me a function for MySQL called substring_index which would allow me to select a certain number of words from a varchar using ' ' as the delimeter, great stuff.

Now I'm using SQLServer instead, and on my latest project i've just gone to use the function, and, :-o, SQLServer doesnt support it, or seem to have a direct equivilent.

Can anyone reccomend the best way to achieve somthing similar in SQL2k5? I'm pretty much a database novice, so if its somthing complex like UDF's, StoredProcs or anything like that then i'll need advice on how -exactly- to create them.

Thanks again guys,

Rob

 

[bborissov]

Can you show us what you want with simple data and desired result.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[TamedTech]

Yeah sure thing Borislav,

Essentialy i just have a long varchar field containing article content, something like this.

Yes, it's tax time. The good news is refunds are about 6 percent over this time last year. So far, the IRS has processed refunds for nearly 39 million taxpayers to the tune of $102 billion. That works out to an average of $2,650 per refund. Sounds like a bundle? Well, one thing you need to understand about tax refunds is that they're larger earlier in the season — that's because people who know they're getting money back have an incentive to file, rather than leaving their hard-earned cash in the coffers of Uncle Sam.

The big question is: What do you do with all that dough? My suggestion. Something good for yourself. Sure, you may feel a need (or at least a desire) to splurge a little bit. We'll talk more about that in a minute. But just take a look at the good you could do for your life, your finances, your psyche or all three by taking any of the following suggestions:

I basicly want to display a short description of the article when i'm listing all of them. So i could just use the LEFT() function, but this isnt great as its charectar based and may cut of in the middle of words. The substring_index function used to allow me to crop text like this using ' ' as the delimeter so it would never crop in the middle of a word.

So the desired result would be somthing like

Yes, it's tax time. The good news is refunds are about 6 percent over this time last year.

Thanks,

Rob

 

[bborissov]

A very simple function, of course you could add another parameter that could be a delimiter. Not very well tested.

CREATE FUNCTION IndexChar(@lcTest varchar(8000), @index int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @retval varchar(8000)
    SET @retval = LEFT(@lcTest, @index)
    IF NOT (RIGHT(@retval,1) = ' '               OR
            SUBSTRING(@lcTest, @index+1,1) = ' ' OR
            SUBSTRING(@lcTest, @index+1,1) = CHAR(13)
             )
       BEGIN
           DECLARE @nextstr varchar(8000)
           SET @nextstr = SUBSTRING(@lcTest, @index+1,8000)
           SET @retval  = @retval + LEFT(@nextstr, CHARINDEX(' ',@nextstr))
       END
  RETURN @retval
END

declare @tte varchar(8000)
SET @tte = 'Yes, it''s tax time. The good news is refunds are about 6 percent over this time last year.
            So far, the IRS has processed refunds for nearly 39 million taxpayers to the tune of $102 billion.
            That works out to an average of $2,650 per refund. Sounds like a bundle?
            Well, one thing you need to understand about tax refunds is that they''re larger earlier in the season — that''s because
            people who know they''re getting money back have an incentive to file, rather than leaving their hard-earned cash in the coffers of Uncle Sam.
            The big question is: What do you do with all that dough?
            My suggestion. Something good for yourself. Sure, you may feel a need (or at least a desire) to splurge a little bit. We''ll talk more about that in a minute. But just take a look at the good you could do for your life, your finances, your psyche or all three by taking any of the following suggestions:'
SELECT dbo.IndexChar(@tte,50), LEFT(@tte,42)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP