SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Returning complete words from a substring
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 02/05/2001 :  10:47:46  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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.

Anonymous
Starting Member

0 Posts

Posted - 02/09/2001 :  11:03:23  Show Profile  Reply with Quote

Posted by MuffinMan

The way you implemented the charIndex function fails when the length of the text in the field is less than the start position.

In the code below, I first set the start position to 15 and run three queries. All three work fine. I then set the start position to 30 and the second query (field name is shortbody_2b) returns nothing. The last query (shortbody_3b) handles the problem.

set nocount on

create table #tmp ( newsitembodytext varchar(50) )

insert #tmp
select 'This is a short example.'

declare @length int

print ''
print ''
print 'Setting trim length to 15 characters'
print ''
select @length = 15

Select LEFT ( newsitembodytext , @length ) as shortbody_1a
from #tmp

Select LEFT ( newsitembodytext , CHARINDEX(' ', newsitembodytext , @length ) ) as shortbody_2a
from #tmp

Select shortbody_3a = Case
When CHARINDEX(' ', newsitembodytext , @length ) = 0
Then LEFT ( newsitembodytext , @length )
Else LEFT ( newsitembodytext , CHARINDEX(' ', newsitembodytext , @length ) )
End
from #tmp


print ''
print ''
print 'Setting trim length to 30 characters'
print ''
select @length = 30

Select LEFT ( newsitembodytext , @length ) as shortbody_1b
from #tmp

Select LEFT ( newsitembodytext , CHARINDEX(' ', newsitembodytext , @length ) ) as shortbody_2b
from #tmp

Select shortbody_3b = Case
When CHARINDEX(' ', newsitembodytext , @length ) = 0
Then LEFT ( newsitembodytext , @length )
Else LEFT ( newsitembodytext , CHARINDEX(' ', newsitembodytext , @length ) )
End
from #tmp


drop table #tmp

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 02/18/2001 :  14:47:25  Show Profile  Reply with Quote
Try this

select Left(Notes,(151-Charindex(' ',Reverse(left(notes,150)))))
FROM titles
WHERE title_id = 'PS2106'

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 02/19/2001 :  06:18:12  Show Profile  Reply with Quote
Left/CharIndex fails on Text DataType. Use PatIndex??

Using CharIndex and Left functions on a Text column causes this error (sql 7) "Argument data type text is invalid for argument 1 of left function". However, PatIndex (msdn: "PATINDEX is useful with text data types") only accepts 2 arguments, so you can't start it at position 150.

PatIndex "...can be used in a WHERE clause in addition to IS NULL, IS NOT NULL, and LIKE (the only other comparisons that are valid on text in a WHERE clause)."

So can you use this method on a Text field?

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 02/22/2001 :  00:55:11  Show Profile  Reply with Quote
RE: Left/CharIndex fails on Text DataType. Use PatIndex??

Try CONVERT()

Go to Top of Page

alemos
Starting Member

Brazil
16 Posts

Posted - 04/14/2004 :  21:34:57  Show Profile  Visit alemos's Homepage  Reply with Quote
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.
Go to Top of Page

grietje
Starting Member

2 Posts

Posted - 02/07/2005 :  01:34:46  Show Profile  Reply with Quote
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.
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 02/07/2005 :  16:17:17  Show Profile  Visit graz's Homepage  Reply with Quote
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.
Go to Top of Page

grietje
Starting Member

2 Posts

Posted - 02/07/2005 :  23:35:22  Show Profile  Reply with Quote
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!
Go to Top of Page

heaversm
Starting Member

1 Posts

Posted - 01/02/2006 :  13:39:38  Show Profile  Visit heaversm's Homepage  Reply with Quote
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
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 01/02/2006 :  20:25:31  Show Profile  Visit graz's Homepage  Reply with Quote
heaversm,

I'd ask this question in the Access forum.

-graz

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 01/03/2006 :  00:51:32  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>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
Go to Top of Page

dcrowell
Starting Member

1 Posts

Posted - 04/15/2006 :  12:31:01  Show Profile  Visit dcrowell's Homepage  Reply with Quote
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
Go to Top of Page

wallacr
Starting Member

United Kingdom
2 Posts

Posted - 09/02/2007 :  21:38:25  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 09/03/2007 :  05:40:48  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Search for split function here

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/03/2007 :  08:12:38  Show Profile  Reply with Quote
"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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/03/2007 :  08:23:31  Show Profile  Reply with Quote
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

Edited by - Kristen on 09/03/2007 08:26:27
Go to Top of Page

wallacr
Starting Member

United Kingdom
2 Posts

Posted - 09/03/2007 :  12:19:24  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 09/03/2007 :  12:28:21  Show Profile  Reply with Quote
"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
Go to Top of Page

markusp
Starting Member

1 Posts

Posted - 12/12/2008 :  10:14:16  Show Profile  Reply with Quote
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


Go to Top of Page

McDoogle
Starting Member

USA
12 Posts

Posted - 03/24/2011 :  11:29:06  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.3 seconds. Powered By: Snitz Forums 2000