Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

Split Text into Charactors In SQL 2008

Hello

What I need to do is split the words in column A into separate Words in Column B.

For Example
Column A "Jobber Drill 2.0mm"
I Need Column B To Read
"jobber obber bber ber er drill rill ill ll 2.0mm .0mm 0mm mm"

This is Each word in column A then the same word again - Dropping the first character until only 2 characters left.

What I am attempting to do is find a way to use full text search to search for any part of a word.
(I have a utility for searching products on a tablet that uses SQL 2008 Express and it is slow when searching with like '%dril%' and like '%2%')

I want to try using contains(description ,'"dril*"' or '"2*"')

Any Other suggestions on speeding the search up would be appreciated.
(It is only returning 5 Columns of data and often only a dozen rows. The Table has 87,000 Records)

Thanks
0
p-plater
Asked:
p-plater
1 Solution
 
Randy PooleCommented:
First off, before trying %search% did you enable full text searching on your database?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why Full Text Search came into your mind? A simple SELECT with a WHERE columnA LIKE '%Word%' won't be good enough?

Anyway, what you are achieving here it's very complicated to do with T-SQL only. As far I can see, I think it's better you use CURSOR. But I will recommend you to use a program language here.

Cheers
0
 
UnifiedISCommented:
This routine will do it, it is a lot of looping but for your test string, I was able to return "Jobber obber bber ber er Drill rill ill ll 2.0mm .0mm 0mm mm"

I did not write the code to update your table, I did this with temporary tables.
--INSERT INTO Test_Jobber(ColumnA)
--SELECT LEN('Jobber Drill 2.0mm')


DECLARE @Temp Table(
	ID int identity,
	ColumnA varchar(100),
	ColumnB varchar(500)
	)

INSERT INTO @Temp (ColumnA)
SELECT ColumnA
FROM Test_Jobber

--SELECT *
--FROM @Temp

DECLARE @ID int
DECLARE @ColumnB varchar(500)

DECLARE @Parsed Table (
	ID	int identity,
	TempID int,
	Word varchar(50),
	Words varchar(500)
	)
DECLARE @Word varchar(50)
DECLARE @Position int
DECLARE @ColumnA varchar(100)

/****/
DECLARE @WordLength int
DECLARE @CurrentWord varchar(50)
DECLARE @CurrentWords varchar(500)
DECLARE @CurrentParsedID int
DECLARE @CurrentPosition int

WHILE (SELECT COUNT(*) FROM @Temp WHERE ColumnB IS NULL) > 0
	BEGIN
		SET @ID = 0
		SET @Word = NULL
		SET @ColumnA = NULL
		SET @ColumnB = NULL

		SELECT @ID = (SELECT TOP 1 ID
					FROM @Temp 
					WHERE ColumnB IS NULL)

		/*parse the value by the space and put in another temp table*/
		SELECT @ColumnA = ColumnA
		FROM @Temp
		WHERE ID = @ID

		SET @ColumnA = LTRIM(RTRIM(@ColumnA)) + ' '
		SET @Position = CHARINDEX(' ', @ColumnA, 1)
		
		IF REPLACE(@ColumnA, ' ', '') <> ''
			BEGIN
				WHILE @Position > 0
				BEGIN
					SET @Word = LTRIM(RTRIM(LEFT(@ColumnA, @Position -1)))
					IF @Word <> ''
						BEGIN	
							INSERT INTO @Parsed (TempID, Word) SELECT @ID, @Word
						END

					SET @ColumnA = RIGHT(@ColumnA, LEN(@ColumnA) - @Position + 1)
					SET @Position = CHARINDEX(' ', @ColumnA, 1)
					IF @Position>0
						CONTINUE
					ELSE
						BREAK
				END
			END
		--now build column b

		WHILE (SELECT COUNT(*) FROM @Parsed
				WHERE Words IS NULL
					AND TempID = @ID) > 0
				BEGIN
					SELECT @CurrentPosition = 0
					SELECT @CurrentParsedID = (SELECT TOP 1 ID
												FROM @Parsed
												WHERE Words IS NULL)
					SELECT @CurrentWord = Word
					FROM @Parsed 
					WHERE ID = @CurrentParsedID
					SELECT @CurrentWords = NULL

					SELECT @WordLength = LEN(@CurrentWord)
					WHILE @WordLength - @CurrentPosition +1 > 2
						BEGIN
							SELECT @CurrentWords = ISNULL(@CurrentWords + ' ', '') + RIGHT(@CurrentWord, @Wordlength - @CurrentPosition)
							
							UPDATE @Parsed
							SET Words = @CurrentWords
							WHERE ID = @CurrentParsedID

							SET @CurrentPosition = @CurrentPosition + 1
							IF @WordLength - @CurrentPosition +1 > 2
								CONTINUE
							ELSE
								BREAK
						END

					IF (SELECT COUNT(*) FROM @Parsed
						WHERE Words IS NULL
						AND TempID = @ID) > 0
						CONTINUE
					ELSE
						BREAK

				END
		SELECT @ColumnB = ISNULL(@ColumnB + ' ', '') + Words
		FROM @Parsed
		WHERE TempID = @ID

		UPDATE @Temp
		SET ColumnB = @ColumnB
		WHERE ID = @ID

		IF (SELECT COUNT(*) FROM @Temp WHERE ColumnB IS NULL) > 0
			CONTINUE
		ELSE
			BREAK

	END

SELECT *
FROM @Temp

SELECT *
FROM @Parsed

Open in new window

0
 
Anthony PerkinsCommented:
So you have a table with millions of rows, which precludes you from using LIKE.

You have attempted to use Full-Text Search, however due to the way that Full-Text functions you cannot use the standard Prefix search and therefore are resorting to this algorithm of splitting the words up.  You are comfortable with creating a new table where you will apply Full-Text Search and populating it with approximately 20x the number of rows compared to the original table.

Am I on the right track?
0
 
p-platerAuthor Commented:
Randy Poole - I Tried Before and After.

Vitor Montalvão - Like is Too Slow

UnifiedIS - Thanks - I'm working with this Routine

Anthony Perkins - I have 87,000 Rows.
            - I am trialling using this Algorithm to populate an additional Field on the same table and building the Full Text Index on the New Field

Thanks All - So Far the search is running about 4 Times faster on this Populated Column than with Like on the Original Column

I am now having an issue with the searching that I have opened another ticket on.
Not sure if any of you can help me with it.

http://www.experts-exchange.com/Database/MS-SQL-Server/SQL_Server_2008/Q_28510336.html

Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now