Keywords Separated using commas or spaces problem

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

SQL Server 2000 Forums

Transact-SQL (2000)

Keywords Separated using commas or spaces problem

Author

Topic

savvy
Starting Member

48 Posts

Posted - 2005-09-26 : 05:53:39

I am using Split function for search engine,Keywords search. This function works fine for the multiple keywords search seperated with spaces or commas but not both. Is there any possibility that i can modify this Split function to accept both spaces and commas.
For example it works fine for
@list ='sap consultant abap' or if I change stored procedure from ' ' to ',' then it works for commas but not for spaces
@list ='sap, consultant, abap'
I want to use both commas and spaces
Thanks in Advance

CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END

in this stored procedure

SELECT DISTINCT J.*
FROM JobDetails AS J
JOIN
(
SELECT DISTINCT J1.JobID
FROM JobDetails AS J1
inner join dbo.Split(@list, ' ') AS S
ON J1.JobPosition LIKE '%' + S.Data + '%'
AND (@place='0' OR J1.Place LIKE '%'+@place+'%')
) J1 ON J1.JobID = J.JobID

Savvy

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-26 : 06:00:33

you can replace all ',' with ' ' when calling the function.
inner join dbo.Split(replace(@list, ',', ' '), ' ')

.......... but would this be ideal what if the search string has a ' ' or a ',' in it?

Duane.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-26 : 06:15:02

As suggested, when passing @RowData replace all comma by space, then use @SplitOn as space

Madhivanan

Failing to plan is Planning to fail

savvy
Starting Member

48 Posts

Posted - 2005-09-26 : 08:41:30

i trying since a while but when i used replace in my function but its specifically giving the result for the ones I used. I am not able to search keywords with alot of spaces after the comma or just with lot of spaces in between the words. I just want my search to be flexible and robust as i have checked most of other search engines do accept these. Is there any possibility like this . Am I going in the right direction.
Thanks for all ur help

Declare @Cnt int
Set @Cnt = 1
Set @RowData = Replace(@RowData, ',' , ' ')
Set @RowData = Replace(@RowData, ', ' , ' ')
While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Savvy

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-26 : 08:49:27

Try that and let us know if you have difficulties

Madhivanan

Failing to plan is Planning to fail

savvy
Starting Member

48 Posts

Posted - 2005-09-26 : 10:22:09

I am able to remove one, two or three spaces by adding more REPLACE statements but my problem is we cant predict the number of spaces in between the words. So is there a way where we can remove the spaces if they are more than one in between the words.
Thanks

Savvy

Kristen
Test

22859 Posts

Posted - 2005-09-26 : 14:34:51

[code]
REPLACE(@RowData, ' ', ',')
[/code]
will replace ALL spaces with commas. Your problem then is if you have commas with on intervening keywords. handle that by NOT inserting into your @RtnValue table any value which is 0 length

Kristen

savvy
Starting Member

48 Posts

Posted - 2005-09-27 : 06:19:50

Thanks for all ur help friends
I used Regex.Replace()in my code which solved my problem.
which is in .NET Framework.
Thanks once again

Savvy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources