given input; search first letter in sentence

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

General SQL Server Forums

New to SQL Server Programming

given input; search first letter in sentence

Author

Topic

richipal
Starting Member

4 Posts

Posted - 2010-08-27 : 05:56:23

Hello All,
I have a sentences table in which each record is id,sentence,
for example If a user provides "ARIA" input I should be able to search this
is my sentences table and output should be sentences line
"A RelativeLayout is a very powerful utility"
"Apple red In Arizona are"
Thanks for your time.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-27 : 06:19:41

Need more explanation to understand. Why don't you put in some sample data and output to make it easier for anyone trying to help you.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-27 : 06:42:58

probably the easiest thing to do would be to precompute the first letters of each sentence into a string and add that to the sentences table.

Then you'd be able to use a simple LIKE to search the table.

Do you need to match on exact order? -- both sentences in your sample start with A then R then I then A .... or do you need to search for that pattern in the middle of a sentence as well.

Please post some sample data and an example query you'd like to perform.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-27 : 06:52:46

To generate a fingerprint of the sentence:



CREATE FUNCTION fingerPrintSentence (
	@sentence VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
	DECLARE @return VARCHAR(MAX)
	DECLARE @stringIndex INT
	DECLARE @curChar CHAR(1)
	DECLARE @firstChar BIT

	SELECT
		@stringIndex = 1
		, @return = LEFT(@sentence, 1)

	WHILE (@stringIndex <= LEN(@sentence))
	SELECT
		@curChar = SUBSTRING(@sentence, @stringIndex, 1)
		, @return = @return + CASE WHEN @firstChar = 1 THEN UPPER(@curChar) ELSE '' END
		, @firstChar = CASE WHEN @curChar = ' ' THEN 1 ELSE 0 END
		, @stringIndex = @stringIndex + 1

	RETURN @return
END

Then you can do something like this


DECLARE @sentence TABLE (
	[sentenceID] INT IDENTITY (1,1) PRIMARY KEY
	, [sentence] VARCHAR(MAX)
	)

INSERT @sentence ([sentence])
      SELECT 'The quick brown fox jumped over the lazy dog.'
UNION SELECT 'Another sentence!'
UNION SELECT 'Yet another Sentence'
UNION SELECT 'I am Sparticus!'


SELECT
	[sentence]
	, dbo.fingerPrintSentence([sentence]) AS [Sentence Fingerprint]
FROM
	@sentence

If you store the fingerprint then you'd be able to use a simple LIKE to search with.

Results


sentence                                           Sentence Fingerprint
-------------------------------------------------- --------------------
Another sentence!                                  AS
I am Sparticus!                                    IAS
The quick brown fox jumped over the lazy dog.      TQBFJOTLD
Yet another Sentence                               YAS

I'm not sure if you can do this with the FULLTEXT queries -- I've never used them.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-27 : 06:58:04

You could, of course use the function to search directly on the tables without precomputing the fingerprint but that would be slow (you'd be table scanning the sentence table every time)

Example




DECLARE @searchString VARCHAR(10)
SET @searchString = '%AS%'

SELECT
	[sentence]
FROM
	@sentence
WHERE
	dbo.fingerPrintSentence([sentence]) LIKE @searchString

Won't return the sentence : The quick brown fox jumped over the lazy dog.

But would return the others.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-27 : 07:45:46

what if he ran a like on the column itself by adjusting what he has to search for ..
something like .. LIKE 'A% R% I% A%'
???

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-08-27 : 07:51:20

ignore that. The above might not suit his requirement

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-27 : 08:00:28

I guess he could do something like that and that would work as long as OP doesn't need to search for patterns inside a sentence. The LIKE certainly works for the two samples posted by the OP.

To be honest -- you Like is probably a better solution that the fingerprint function. I just got a little carried away!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

richipal
Starting Member

4 Posts

Posted - 2010-08-27 : 08:10:02

Thanks for help, I appreciate your time Charlie and sakets.
I am using sqlite and writing an application in android.
The application searches sentences which match the first letters of each word, given an input.
Declare function wont work as its sqlite.
Say someone type in AS
the following sentences will show as result, AS will match anywhere in the sentence

Another sentence! AS
I am Sparticus! %AS
Yet another Sentence %AS
Apple Syrup is good AS%
Is Apple Syrup good for health %AS%

sample query:
input = 'AS'
I want the results as above If I do select * from sentences.

Thanks again.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-27 : 09:18:19

this is a Microsoft sql server forum.

You might be better trying dbforums

http://www.dbforums.com/

good luck

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

richipal
Starting Member

4 Posts

Posted - 2010-08-27 : 09:38:40

Thanks for your time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources