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 2008 Forums
 Transact-SQL (2008)
 Search by comma separted string in database

Author  Topic 

romah
Starting Member

3 Posts
Posted - 2009-12-22 : 13:26:25
I have to implement a search button in my asp.net application.
If the user enters string in search text field and clicks the Search button, it will search the skill in database table and return the data [if found].

Here is my database table

Emp_Id-------Name---------Address--------Phone--------------Skill
1------------Adam---------New York-------242-234-2433-------C,C++,JAVA,JSP,JSF
2------------Smith--------New Jersey-----232-345-5645-------C#,ASP.NET,XML,SQL Sever
3------------John---------North Iowa-----465-567-7543-------SQL Server,C#,Perl
4------------Ajay---------New York-------456-576-5756-------VB.NET,SQL Server,ASP.NET,J#
5------------Tony---------Indiana--------353-533-4665-------Oracle,Sybase,XML
6------------Bush---------Iowa-----------466-354-3543-------C++,C#,Ruby,Ruby on Rails
7------------Jack---------Maryland-------345-456-2432-------VisualC++,Oracle
8------------Rose---------Virginia-------543-345-3222-------Classic ASP,MS Access, SQL Server
9------------Steve--------Seattle--------425-235-4533-------VB6,MS Access,C,C++
10-----------Albert-------California-----345-465-3454-------Java,SQL Server
11-----------Sam----------Utah-----------802-432-3452-------ASP.NET,PowerShell,XML,XSLT
12-----------Robert-------Texas----------345-354-3534-------ASP.NET,C#
13-----------Dennis-------Florida--------345-456-2432-------VB.NET,MS Access
14-----------Sheldon------Portland-------426-453-5644-------SQL Server, ASP.NET, C#, XML, Java, C, C++
15-----------Bronson------Chicago--------634-345-5645-------Flash, DreamWeaver, Photoshop, Frontpage



If user searches the skill with the search string "C#, ASP.NET, SQL Server", it should return the result like this [All words matched in search string comes first]
So, It is in descending order based on Skill found.

Emp_Id-------Name---------Address--------Phone--------------Skill
2------------Smith--------New-Jersey-----232-345-5645-------C#, ASP.NET, XML, SQL Sever
14-----------Sheldon------Portland-------426-453-5644-------SQL Server, ASP.NET, C#, XML, Java, C, C++
3------------John---------North-Iowa-----465-567-7543-------SQL Server, C#, Perl
4------------Ajay---------New-York-------456-576-5756-------VB.NET, SQL Server, ASP.NET, J#
12-----------Robert-------Texas----------345-354-3534-------ASP.NET, C#
6------------Bush---------Iowa-----------466-354-3543-------C++, C#, Ruby, Ruby on Rails
8------------Rose---------Virginia-------543-345-3222-------Classic ASP, MS Access, SQL Server
10-----------Albert-------California-----345-465-3454-------Java, SQL Server
11-----------Sam----------Utah-----------802-432-3452-------ASP.NET, PowerShell, XML, XSLT




If user searches the skill with the search string "C#, SQL Server", it should return the result like this [All words matched in search string comes first]

Emp_Id-------Name--------Address---------------Phone--------Skill
2------------Smith--------New-Jersey-----232-345-5645-------C#, ASP.NET, XML, SQL Sever
3------------John---------North-Iowa-----465-567-7543-------SQL Server, C#, Perl
14-----------Sheldon------Portland-------426-453-5644-------SQL-Server, ASP.NET, C#, XML, Java, C, C++
6------------Bush---------Iowa-----------466-354-3543-------C++, C#, Ruby, Ruby on Rails
8------------Rose---------Virginia-------543-345-3222-------Classic ASP, MS Access, SQL Server
10-----------Albert-------California-----345-465-3454-------Java, SQL Server



I have created a storeprocedure which accepts a search string [the string may be comma separated string].


create procedure [dbo].[Proc_SearchBySkill]
(
@skill varchar(200)
)

as
begin

select * from Employee
where Skill like '%'+ @skill +'%'

end


I need to change this procedure to work for comma separted string too.

what to do??? I need your great help.

Thanks in advance.

Romah

ehorn
Master Smack Fu Yak Hacker

1632 Posts
Posted - 2009-12-22 : 15:16:21
How about something like this...

--problem data set

SELECT 
	1 AS empID, 
	'Adam' AS name, 
	'New York' AS [address], 
	'242-234-2433' AS phone, 
	'C,C++,JAVA,JSP,JSF' AS skill 
INTO #problem 
	UNION ALL SELECT 2, 'Smith','New Jersey','232-345-5645', 'C#,ASP.NET,XML,SQL Server'
	UNION ALL SELECT 3, 'John','North Iowa','465-567-7543', 'SQL Server,C#,Perl' 
	UNION ALL SELECT 4, 'Ajay','New York', '456-576-5756', 'VB.NET,SQL Server,ASP.NET,J#'
	UNION ALL SELECT 5, 'Tony','Indiana', '-353-533-4665', 'Oracle,SybASe,XML'
	UNION ALL SELECT 6, 'Bush','Iowa', '466-354-3543', 'C++,C#,Ruby,Ruby on Rails'
	UNION ALL SELECT 7, 'Jack','Maryland', '345-456-2432', 'VisualC++,Oracle'
	UNION ALL SELECT 8, 'Rose','Virginia', '543-345-3222', 'ClASsic ASP,MS Access, SQL Server'
	UNION ALL SELECT 9, 'Steve','Seattle', '-425-235-4533', 'VB6,MS Access,C,C++'
	UNION ALL SELECT 1, 'Albert','California','345-465-3454', 'Java,SQL Server'
	UNION ALL SELECT 11, 'Sam','Utah', '802-432-3452', 'ASP.NET,PowerShell,XML,XSLT'
	UNION ALL SELECT 12, 'Robert','TexAS', '345-354-3534', 'ASP.NET,C#'
	UNION ALL SELECT 13, 'Dennis', 'Florida', '345-456-2432', 'VB.NET,MS Access'
	UNION ALL SELECT 14, 'Sheldon','Portland', '426-453-5644', 'SQL Server, ASP.NET, C#, XML, Java, C, C++'
	UNION ALL SELECT 15, 'Bronson','Chicago', '634-345-5645', 'FlASh, DreamWeaver, Photoshop, Frontpage'


--function for parsing

CREATE FUNCTION dbo.fnParseCSV (@String VARCHAR(8000), @Delimeter CHAR(1)) 
RETURNS TABLE 
AS
RETURN 
(
	SELECT 
		LTRIM(RTRIM(NULLIF(SUBSTRING(@Delimeter + @String + @Delimeter, Number, CHARINDEX(@Delimeter, @Delimeter + @String + @Delimeter, Number) - Number), ''))) AS skill
	FROM 
		master..spt_values 
	WHERE 
		Type = 'P' 
			AND Number <= LEN(@Delimeter + @String + @Delimeter)
			AND SUBSTRING(@Delimeter + @String + @Delimeter, Number - 1, 1) = @Delimeter
			AND CHARINDEX(@Delimeter, @Delimeter + @String + @Delimeter, Number) - Number > 0
)
GO

--search value

DECLARE @searchVal varchar(100);
SELECT @searchVal =  'C#, ASP.NET, SQL Server'

--solution

	SELECT 
		p.empID,
		p.name,
		p.[address],
		p.phone,
		p.skill,
		COUNT(s.skill) skill, 
		COUNT(sm.skill) skillMatch
	FROM 
		#problem p
		CROSS APPLY dbo.fnParseCSV(skill,',') s
		LEFT JOIN dbo.fnParseCSV(@searchVal,',') sm ON sm.skill = s.skill
	GROUP BY		
		p.empID,
		p.name,
		p.[address],
		p.phone,
		p.skill
	ORDER BY 
		COUNT(sm.skill) DESC,
		COUNT(s.skill) DESC

--clean up

DROP TABLE #problem
DROP FUNCTION dbo.fnParseCSV
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2009-12-22 : 15:40:46
Glad to have you back Jay!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

ehorn
Master Smack Fu Yak Hacker

1632 Posts
Posted - 2009-12-22 : 17:19:09
quote:
Originally posted by tkizer

Glad to have you back Jay!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."


Thanks Tara, Big project coming up next year and I am RUSTY!!!

That said, this is the place to be to tune up :)

Happy Holiday's
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site