derekmx271 derekmx271 - 1 year ago 71
SQL Question

How to get the first n sentences from a string in T-SQL?

I am trying to populate our "short description" field using the "full description" field. Basically, I want to set the

column equal to the first three sentences from the

I know how I would do this in C#, but I am having a little trouble getting it done in my SQL query. I don't care about performance- as this query will only be ran one time to generate this temporary data. So, any and all solutions will work just find for us!

My attempt:

UPDATE Product
SET ShortDescription = (
WHEN (LEN(FullDescription) - LEN(REPLACE(FullDescription, '.', ''))) >= 3 THEN
LEFT(str, pos)
FullDescription AS str,
CHARINDEX('.', FullDescription) AS pos
) x
WHERE FullDescription IS NOT NULL;

Unfortunately, the above query only gets the first sentence. I can't seem to figure out how to find the CHARINDEX of the third period. Anyone know a clean and easy way to locate this character?

Also, am I correct in my assumption that the periods are really the only way to identify sentences? I am concerned that (in some rare occasions), that there may be decimals in the sentences, which would provide some awful descriptions like: "This product is great. It has awesome features. It is 2."...

Any direction or feedback is greatly appreciated! Thank you!

Answer Source

I have a TVF which may help. If you don't want the UDF, the code can easily be ported into the Cross Apply.

I should note. This delimiter is a period followed by a space. Just now thinking it would not capture other punctuation (i,e. !?)

Declare @String varchar(max) ='This is sentance one. This is sentance two.  This is Sentence 3.  This is sentecne 4.'
Declare @YourTable table (ID int,FullDescription varchar(max))
Insert Into @YourTable values
(1,'Some sentance with a decimal like 25.26 is OK. Sentance number two.  Sentance number 3. Sentance number 4 would not be included.'),
(2,'I know how I would do this in C#.  I am having a little trouble getting it done in my SQL query. I don''t care about performance. This query will only be ran one time.')

Select A.*
 From @YourTable A
 Cross Apply (Select ShortDescription=concat(Pos1,'. ',Pos2,'. ',Pos3,'.') From [dbo].[udf-Str-Parse-Row](A.FullDescription,'. ')) B

Returns enter image description here

The UDF if desired

CREATE FUNCTION [dbo].[udf-Str-Parse-Row] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
Return (
    Select Pos1 = xDim.value('/x[1]','varchar(max)')
          ,Pos2 = xDim.value('/x[2]','varchar(max)')
          ,Pos3 = xDim.value('/x[3]','varchar(max)')
          ,Pos4 = xDim.value('/x[4]','varchar(max)')
          ,Pos5 = xDim.value('/x[5]','varchar(max)')
          ,Pos6 = xDim.value('/x[6]','varchar(max)')
          ,Pos7 = xDim.value('/x[7]','varchar(max)')
          ,Pos8 = xDim.value('/x[8]','varchar(max)')
          ,Pos9 = xDim.value('/x[9]','varchar(max)')
     From (Select Cast('<x>' + Replace(@String,@Delimiter,'</x><x>')+'</x>' as XML) as xDim) A
--Select * from [dbo].[udf-Str-Parse-Row]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse-Row]('John Cappelletti',' ')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download