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
p-platerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.