Article: Returning complete words from a substring

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Returning complete words from a substring

Author Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-02-05 : 10:47:46

melanie writes "I am trying to return a string using Select *, left (newsitembodytext, 150) . . . As you can guess it is returning incomplete words. I need the last word to be complete any ideas?" I think we can put some string functions to use and solve this one.

Article Link.

alemos
Starting Member

16 Posts

Posted - 2004-04-14 : 21:34:57

Geez, last comment was in 2001! Oh well, here goes my 2c:

None of these solutions work in my case. You see, my records have at most 50 characters. Lets take this example:

A brown fox jumped over the lazy dog.

There are 37 characters in that sentence. If I try using 35 as my max size for the short descriptions, I would get this:

A brown fox jumped over the lazy do

because there are no spaces after the word DOG. You can sort of fix this by appending a space at the end of the sentence

Example:
select Left(Notes,(151-Charindex(' ',Reverse(left(notes + ' ',150)))))

Then it is able to find the space character correctly.

grietje
Starting Member

2 Posts

Posted - 2005-02-07 : 01:34:46

My delimiter is not a space but a /

With this query:


SELECT ISSUE_LABEL, ISSUE_YEAR, LEFT(ISSUE_LABEL, CHARINDEX('/', ISSUE_LABEL)) AS monthID 
FROM VIEWISSUES

the returned values look like this:
2/5 - 2/
2/4 - 2/
2/3 - 2/
2/2 - 2/
2/1 - 2/

I need to get rid of the /
So I tried


CHARINDEX('/', ISSUE_LABEL, 2)

or 

CHARINDEX('/', ISSUE_LABEL, 1)

but the / stays.

I have to group the issues of a daily newspaper by month using a field called ISSUE_LABEL that can be 1/1 (for January 1st) to 12/31 (for Dec. 31th) or 7/12 (Jul 12) or 12/7 (Dec 7). I cannot influence the input or structure of the database so I need to work with it.

I would be nice if I can use the monthID as output.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-02-07 : 16:17:17

Why not just use the MONTH function?

SELECT ISSUE_LABEL, ISSUE_YEAR, MONTH(ISSUE_LABEL) AS monthID
FROM VIEWISSUES

or if ISSUE_LABEL is a string ...

SELECT ISSUE_LABEL, ISSUE_YEAR, MONTH(CONVERT(DATETIME, ISSUE_LABEL)) AS monthID
FROM VIEWISSUES

Of course this assumes that ISSUE_LABEL is a valid datetime value.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

grietje
Starting Member

2 Posts

Posted - 2005-02-07 : 23:35:22

graz, that's exactly what I have done, there was a date field as well in the tabel which I could use to get the required results.

Thanks!

heaversm
Starting Member

1 Post

Posted - 2006-01-02 : 13:39:38

I am trying to do something fairly simple - I need to select the first word from one field and (preferrably) have that first word inserted into a new field (in Microsoft Access). I was attempting to use this statement:

SELECT LEFT(Field3,CHARINDEX(' ',Field3,20)) AS shortbody
FROM froogle_data;

but access doesn't recognize the function CHARINDEX. Is there another way to do this?

Thanks!

Mike

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2006-01-02 : 20:25:31

heaversm,

I'd ask this question in the Access forum.

-graz

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-03 : 00:51:32

>>but access doesn't recognize the function CHARINDEX. Is there another way to do this?

In Access you have to use InStr function

Select left(Field3,instr(Field3,' ')-1) from froogle_data

Madhivanan

Failing to plan is Planning to fail

dcrowell
Starting Member

1 Post

Posted - 2006-04-15 : 12:31:01

When you nest CHARINDEX inside the LEFT function you will get error "Msg 536, Invalid length parameter passed to the SUBSTRING function." if the character expression you look for is not part of the string you are looking in. The solution is to add a where clause that ensures the CHARINDEX is greater than zero.

SELECT LEFT(Field3,CHARINDEX(' ',Field3,20)) AS shortbody
FROM froogle_data
WHERE (CHARINDEX(' ',Field3,20) > 0);

Danny Crowell, MCSD
www.crowsol.com

wallacr
Starting Member

2 Posts

Posted - 2007-09-02 : 21:38:25

Hi,

This is a very helpful article, thank you.

I'm trying to do something simular, but using the RIGHT function and unfortunately I'm not getting any where, although you article has helped me a lot but I'm stuck on adaptin it to display only the last four words (complete) from a database field.

So far I have:

SELECT TOP 1 RIGHT(text, CHARINDEX(' ', text, 30)) as t4 FROM STORY WHERE paid IS NOT Null ORDER BY paid Desc;

Many thanks in advance for any help or advice.

Kind regards,

Rich

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-03 : 05:40:48

Search for split function here

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-03 : 08:12:38

"Search for split function here"

Or better still here!

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Splitting%20delimited%20lists,Best%20split%20functions

Kristen

Kristen
Test

22859 Posts

Posted - 2007-09-03 : 08:23:31

Here's how I split individual words into a separate "Name Lookup" table:


DECLARE	@tblNameList TABLE
(
	T_nlu_Name	varchar(8000)
)

-- Change all non-alphanumeric to SPACE
SELECT	@strNameList = dbo.MyFN_ReplacePattern(@strNameList, '[^a-zA_Z0-9 ]', ' ', NULL)

INSERT INTO @tblNameList
SELECT	DISTINCT [T_nlu_Name] = value
FROM	dbo.MyFN_Split(@strNameList, ' ')	-- ' ' Space delimiter

-- Delete names which are NO LONGER in the NameList
DELETE	D
FROM	dbo.MyNameLookupTable AS D
	LEFT OUTER JOIN @tblNameList
		 ON T_nlu_Name = nlu_Name
WHERE	    nlu_Source = @strSource
	AND nlu_ID = @ID
	AND T_nlu_Name IS NULL

-- Insert new names
INSERT INTO dbo.MyNameLookupTable
SELECT DISTINCT
	nlu_Source	= @strSource,		-- Source (table/column) [PK] 
	nlu_ID		= @ID,			-- Record ID [PK] 
	nlu_Name	= T_nlu_Name		-- Name
FROM	@tblNameList
	LEFT OUTER JOIN dbo.MyNameLookupTable
		 ON nlu_Name = T_nlu_Name
		AND nlu_Source = @strSource
		AND nlu_ID = @ID
WHERE	    nlu_Name IS NULL

Which needs a "replace pattern" function - used to replace any non-alphanumeric character with a space, so Hyphen etc. all become word boundaries


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[MyFN_ReplacePattern]') 
	AND xtype IN (N'FN', N'IF', N'TF'))
		DROP FUNCTION dbo.MyFN_ReplacePattern
GO

CREATE FUNCTION dbo.MyFN_ReplacePattern
(
	@strData		varchar(8000),	-- String Data
	@strReplacePattern	varchar(8000),	-- Character set to remove - e.g. '[;,.]' or '[^A-Za-z0-9]'
	@strReplaceWith		varchar(8000)	-- Replace string - Empty string to remove, or  e.g. ' '
						-- Make sure that the Replace Pattern includes the ReplaceString!
)
RETURNS varchar(8000)
AS
/*
 * MyFN_ReplacePattern	Remove Character Set from a String
 *
 * Returns:
 *
 * varchar(8000)
 *
 * HISTORY:
 *
 * 31-Oct-2005 Started
 */
BEGIN
DECLARE	@intLoop	int

	SELECT	@strReplacePattern = '%' + @strReplacePattern + '%'
	SELECT	@intLoop = PATINDEX(@strReplacePattern, @strData)
	WHILE @intLoop > 0
	BEGIN
		SELECT @strData = STUFF(@strData, @intLoop, 1, @strReplaceWith)
		SELECT @intLoop = PATINDEX(@strReplacePattern, @strData)
	END

	RETURN @strData

/** TEST RIG

SELECT	dbo.MyFN_ReplacePattern('!ABC;DEF<>xyz?', '[^a-zA_Z0-9]', '', NULL)

SELECT	dbo.MyFN_ReplacePattern('!ABC;DEF<>xyz?', '[;<>]', '', NULL)

SELECT	dbo.MyFN_ReplacePattern('   123456 .', '[^0-9]', '', NULL)

SELECT	dbo.MyFN_ReplacePattern('John, Smith-Jones', '[^a-zA_Z0-9 ]', ' ', NULL)

**/
--==================== MyFN_ReplacePattern ====================--
END
GO

Kristen

wallacr
Starting Member

2 Posts

Posted - 2007-09-03 : 12:19:24

Thank you for the responses, it gives me something to look into. It does seem a bit more complicated than I first expected.

Cheers,

Rich

Kristen
Test

22859 Posts

Posted - 2007-09-03 : 12:28:21

"It does seem a bit more complicated than I first expected"

You are not wrong there!

If you are more comfortable with Application language, rather than SQL, you might want to do the "splitting" into individual words there, and Insert each one into the database - perhaps via individual INSERT statements (which will be relatively slow, one-by-one from the application, but may be fast enough for your needs), or sending as an XML list or some-such which can be readily imported into a table with some simple SQL statements

Kristen

markusp
Starting Member

1 Post

Posted - 2008-12-12 : 10:14:16

Simple SQL Split function

SELECT * FROM [dbo].[SPLIT]('1,2,3')

CREATE FUNCTION [dbo].[SPLIT]
(
@Text TEXT
)
RETURNS @output TABLE(
Item INT
)
BEGIN

DECLARE @start INT, @end INT , @Datalen INT

SELECT @start = 1,
@end = CHARINDEX(',', @Text),
@Datalen = DATALENGTH(@Text) + 1

WHILE @start < @Datalen BEGIN
IF @end <= @start BEGIN
SET @end = @Datalen
END

INSERT INTO @output (Item)
VALUES (SUBSTRING(@Text, @start, @end - @start))

SET @start = @end + 1
SET @end = CHARINDEX(',', SUBSTRING(@Text, @start,20)) + @start -1
END
RETURN
END

And here get 2 fields

SELECT * FROM [dbo].[SPLIT_2]('1_1,2_1,2_2')

CREATE FUNCTION [dbo].[SPLIT_2]
(
@Text TEXT
)
RETURNS @output TABLE(
Document_id INT,IndexOf int
)
BEGIN

DECLARE @start INT, @end INT , @Datalen INT ,
@Values VARCHAR(50), @Document VARCHAR(5),
@Index VARCHAR(5), @CharIndex INT

SELECT @start = 1,
@end = CHARINDEX(',', @Text),
@Datalen = DATALENGTH(@Text) + 1

WHILE @start < @Datalen BEGIN
IF @end <= @start BEGIN
SET @end = @Datalen
END

SET @Values = SUBSTRING(@Text, @start, @end - @start)
SET @CharIndex = CHARINDEX('_', @Values)

SET @Document = SUBSTRING(@Values,0,@CharIndex)
SET @Index = SUBSTRING(@Values,@CharIndex+1,5)

INSERT INTO @output (document_id , IndexOf)
VALUES (@Document,@Index)

SET @start = @end + 1
SET @end = CHARINDEX(',', SUBSTRING(@Text, @start,20)) + @start -1
END
RETURN
END

McDoogle
Starting Member

12 Posts

Posted - 2011-03-24 : 11:29:06

I'm trying to do something similiar to how this post started. Instead of 150 characters I want to return the first 30, however I also do not want to cut any words off. So if the word is going to get cut off, leave the word out and just stop at the previous word. This is just one example, the descriptions vary in length.

"Scarf - Winter Coat Faux Fur Trim" should be returned as

"Scarf - Winter Coat Faux Fur" because the the word Trim exceeds 30 chacters.

This was my first attempt:
select left (itemdescription1, charindex(' ',itemdescription1,30)) as shortened
from inmast

It did not work, and below is why.
That’s a good thought but it finds the FIRST space AFTER 30 chars. I want the LAST space BEFORE 30. Also, I hate looking for ‘ ‘ because it can be hard to tell the difference between ‘ ‘ and ‘’, depending on the font. Use char(32) instead. That’s just to make the code easier, there is no functional difference.

Any help would be appriciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources