| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
 Posted - 02/09/2001 : 11:03:23
|
| 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
|
 |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
alemos
Starting Member
Brazil
16 Posts |
Posted - 04/14/2004 : 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 - 02/07/2005 : 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
USA
4128 Posts |
Posted - 02/07/2005 : 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 - 02/07/2005 : 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 Posts |
Posted - 01/02/2006 : 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
USA
4128 Posts |
Posted - 01/02/2006 : 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
India
22460 Posts |
Posted - 01/03/2006 : 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 Posts |
Posted - 04/15/2006 : 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
United Kingdom
2 Posts |
Posted - 09/02/2007 : 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
India
22460 Posts |
Posted - 09/03/2007 : 05:40:48
|
Search for split function here
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/03/2007 : 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 |
Edited by - Kristen on 09/03/2007 08:26:27 |
|
|
|
wallacr
Starting Member
United Kingdom
2 Posts |
Posted - 09/03/2007 : 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
United Kingdom
22191 Posts |
Posted - 09/03/2007 : 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 Posts |
Posted - 12/12/2008 : 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
USA
12 Posts |
Posted - 03/24/2011 : 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. |
|
|
| |
Topic |
|
|
|
Article: Returning complete words from a substring
