Search several fields(resolved

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

Search several fields(resolved

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-01-15 : 05:08:36

I want to create a search form. My table has 4 address fields and a postcode field

Add1
Add2
Add3
Add4
PCode

i want the user to be able to enter part of an address and to search all the fields. However, if they enter 3 High 3 may be in Add1 and Bay in Add2 or 3 Bay may all be in Add1.

I think I need to build a string of all the fields and find if the search criteria entered is in that string. How do I do this in an sp ? Here's my current sp.

ALTER PROCEDURE [dbo].[SearchAllAddress]
-- Add the parameters for the stored procedure here
@Address as nvarchar(100)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT * from Register
WHERE (Add1 like '%'+@Address+'%'
or Add2 like '%'+@Address+'%'
or Add3 like '%'+@Address+'%'
or Add4 like '%'+@Address+'%'
or Pcode like '%'+@Address+'%' )

END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-15 : 05:15:17

Is there a question here?

E 12°55'05.63"
N 56°04'39.26"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-01-15 : 05:34:37

quote:
I think I need to build a string of all the fields and find if the search criteria entered is in that string. How do I do this in an sp ?

In an sp I need to know how I do this

@AlltheAddress = Add1+Add2+Add3+Add4+Pcode

then @AlltheAddress is searched for the parameter as if they enter '3 High' 3 may be in Add1 and High in Add2 or '3 High' may all be in Add1.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-15 : 05:42:02

Are you using sql server 2005?

E 12°55'05.63"
N 56°04'39.26"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-01-15 : 05:50:02

Yes, I am and a windows form vs2005 is calling the sp from a tableadapter

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-15 : 06:02:15

Function fnParseList is found here on SQLTeam.

ALTER PROCEDURE dbo.SearchAllAddress
(
	@Address as nvarchar(100)
)
AS

SET NOCOUNT ON

DECLARE	@Parts TABLE
	(
		data
	)

INSERT	@Parts
	(
		data
	)
SELECT	data
FROM	dbo.fnParseList(' ', @Address)

SELECT DISTINCT	r.*
FROM		@Parts AS p
CROSS JOIN	Register AS r
WHERE		r.Add1 LIKE '%' + p.data + '%'
		OR r.Add2 LIKE '%' + p.data + '%'
		OR r.Add3 LIKE '%' + p.data + '%'
		OR r.Add4 LIKE '%' + p.data + '%'
		OR r.Pcode LIKE '%' + p.data + '%'

E 12°55'05.63"
N 56°04'39.26"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-01-15 : 06:34:50

Thanks. Now, I found the function, but which type of fuction do I store it in ? Table, scalar,aggregate or system ? Do I have to do anythig else other than put it in one of these and call it as above ?

CREATE FUNCTION dbo.fnParseList
(
@Delimiter CHAR,
@Text VARCHAR(8000)
)
RETURNS @Result TABLE (RowID SMALLINT IDENTITY(1, 1) PRIMARY KEY, Data VARCHAR(8000))
AS

BEGIN
DECLARE @NextPos SMALLINT,
@LastPos SMALLINT

SELECT @NextPos = 0

WHILE @NextPos <= DATALENGTH(@Text)
BEGIN
SELECT @LastPos = @NextPos,
@NextPos = CASE
WHEN CHARINDEX(@Delimiter, @Text, @LastPos + 1) = 0 THEN DATALENGTH(@Text) + 1
ELSE CHARINDEX(@Delimiter, @Text, @LastPos + 1)
END

INSERT @Result
(
Data
)
SELECT SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)
END

RETURN
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-15 : 06:49:17

You should use the second version of fnParseList and execute that function code in your database.

E 12°55'05.63"
N 56°04'39.26"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-01-15 : 07:03:10

Sorry, but where do I find the 'second' version ? I seem to have got it working, but if I enter Bay View as my parameter, as well as Bay View addresses it displays High View and Sea View. I assume because of the word View. How do I treat my parameter as 'Bay View' and exclude all other 'Views' ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-15 : 07:11:44

[code]ALTER PROCEDURE dbo.SearchAllAddress
(
@Address as nvarchar(100)
)
AS

SET NOCOUNT ON

DECLARE @Parts TABLE
(
data
)

INSERT @Parts
(
data
)
SELECT data
FROM dbo.fnParseList(' ', @Address)

DECLARE @n INT

SELECT @n = COUNT(*)
FROM @Parts

DECLARE @Hit TABLE
(
ID INT
)

INSERT @Hit
(
ID
)
SELECT r.ID
FROM @Parts AS p
CROSS JOIN Register AS r
WHERE r.Add1 LIKE '%' + p.data + '%'
OR r.Add2 LIKE '%' + p.data + '%'
OR r.Add3 LIKE '%' + p.data + '%'
OR r.Add4 LIKE '%' + p.data + '%'
OR r.Pcode LIKE '%' + p.data + '%'
GROUP BY r.ID
HAVING COUNT(*) >= @n

SELECT r.*
FROM Register AS r
INNER JOIN @Hit AS h ON h.ID = r.ID[/code]

E 12°55'05.63"
N 56°04'39.26"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-01-15 : 07:23:51

Sorry about this.......Bay view works fine now, but if I just enter View - nothing is returned. Users could enter 2 or 2 Bay or 2 Bay View.

Here's the function

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[fnParseList]
(
@Delimiter CHAR,
@Text TEXT
)
RETURNS @Result TABLE (RowID SMALLINT IDENTITY(1, 1) PRIMARY KEY, Data VARCHAR(8000))
AS

BEGIN
DECLARE @NextPos INT,
@LastPos INT

SELECT @NextPos = CHARINDEX(@Delimiter, @Text, 1),
@LastPos = 0

WHILE @NextPos > 0
BEGIN
INSERT @Result
(
Data
)
SELECT SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)

SELECT @LastPos = @NextPos,
@NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1)
END

IF SCOPE_IDENTITY() > 0
INSERT @Result
(
Data
)
SELECT SUBSTRING(@Text, @LastPos + 1, DATALENGTH(@Text) - @LastPos)

RETURN
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-15 : 08:57:04

Works for me...

-- Prepare sample data
DECLARE	@Register TABLE
	(
		id INT IDENTITY(1, 1),
		add1 VARCHAR(20),
		add2 VARCHAR(20),
		add3 VARCHAR(20),
		add4 VARCHAR(20),
		Pcode VARCHAR(20)
	)

INSERT	@Register
SELECT	'3 bay view', NULL, NULL, NULL, NULL UNION ALL
SELECT	'view', '3', 'north', NULL, NULL UNION ALL
SELECT	'peso', 'sqlteam', NULL, NULL, NULL UNION ALL
SELECT	'sea', NULL, '3', NULL, 'view'

DECLARE	@Address VARCHAR(30)

SET	@Address = 'view 3'

DECLARE	@Parts TABLE
	(
		data VARCHAR(20)
	)

INSERT	@Parts
	(
		data
	)
SELECT	data
FROM	dbo.fnParseList(' ', @Address)

DECLARE	@n INT

SELECT	@n = COUNT(*)
FROM	@Parts

DECLARE	@Hit TABLE
	(
		ID INT
	)

INSERT		@Hit
		(
			ID
		)
SELECT		r.ID
FROM		@Parts AS p
CROSS JOIN	@Register AS r
WHERE		r.Add1 LIKE '%' + p.data + '%'
		OR r.Add2 LIKE '%' + p.data + '%'
		OR r.Add3 LIKE '%' + p.data + '%'
		OR r.Add4 LIKE '%' + p.data + '%'
		OR r.Pcode LIKE '%' + p.data + '%'
GROUP BY	r.ID
HAVING		COUNT(*) >= @n

SELECT		@address,
		r.*
FROM		@Register AS r
INNER JOIN	@Hit AS h ON h.ID = r.ID

E 12°55'05.63"
N 56°04'39.26"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-01-15 : 09:26:51

Sorry, don't think I'm explaining too well.Here's sample data

Add1 3
Add2 Bay View
Add3 Townsville
Add4
Pcode DS347HH

if I enter 3 Bay View it doesn't find anything

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-15 : 09:29:47

Works for me, again.

-- Prepare sample data
DECLARE	@Register TABLE
	(
		id INT IDENTITY(1, 1),
		add1 VARCHAR(20),
		add2 VARCHAR(20),
		add3 VARCHAR(20),
		add4 VARCHAR(20),
		Pcode VARCHAR(20)
	)

INSERT	@Register
SELECT	'bay 3 view', NULL, NULL, NULL, NULL UNION ALL
SELECT	'view', '3', 'north', NULL, NULL UNION ALL
SELECT	'peso', 'sqlteam', NULL, NULL, NULL UNION ALL
SELECT	'sea', NULL, '3', NULL, 'view'

INSERT	@Register
SELECT	'3', 'Bay View', 'Townsville', NULL, 'DS347HH'

DECLARE	@Address VARCHAR(30)

SET	@Address = '3 bay view'

DECLARE	@Parts TABLE
	(
		data VARCHAR(20)
	)

INSERT	@Parts
	(
		data
	)
SELECT	data
FROM	dbo.fnParseList(' ', @Address)

DECLARE	@n INT

SELECT	@n = COUNT(*)
FROM	@Parts

DECLARE	@Hit TABLE
	(
		ID INT
	)

INSERT		@Hit
		(
			ID
		)
SELECT		r.ID
FROM		@Parts AS p
CROSS JOIN	@Register AS r
WHERE		r.Add1 LIKE '%' + p.data + '%'
		OR r.Add2 LIKE '%' + p.data + '%'
		OR r.Add3 LIKE '%' + p.data + '%'
		OR r.Add4 LIKE '%' + p.data + '%'
		OR r.Pcode LIKE '%' + p.data + '%'
GROUP BY	r.ID
HAVING		COUNT(*) >= @n

SELECT		@address,
		r.*
FROM		@Register AS r
INNER JOIN	@Hit AS h ON h.ID = r.ID

E 12°55'05.63"
N 56°04'39.26"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-01-15 : 09:41:26

That sp above does work when you build the table like that but I can't seem to adapt it so it works on mytable :-(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-15 : 09:46:23

[code]ALTER PROCEDURE dbo.SearchAllAddress
(
@Address AS NVARCHAR(100)
)
AS

DECLARE @Parts TABLE
(
data VARCHAR(20)
)

INSERT @Parts
(
data
)
SELECT data
FROM dbo.fnParseList(' ', @Address)

DECLARE @n INT

SELECT @n = COUNT(*)
FROM @Parts

DECLARE @Hit TABLE
(
ID INT
)

INSERT @Hit
(
ID
)
SELECT r.ID
FROM @Parts AS p
CROSS JOIN Register AS r
WHERE r.Add1 LIKE '%' + p.data + '%'
OR r.Add2 LIKE '%' + p.data + '%'
OR r.Add3 LIKE '%' + p.data + '%'
OR r.Add4 LIKE '%' + p.data + '%'
OR r.Pcode LIKE '%' + p.data + '%'
GROUP BY r.ID
HAVING COUNT(*) >= @n

SELECT r.*
FROM Register AS r
INNER JOIN @Hit AS h ON h.ID = r.ID[/code]

E 12°55'05.63"
N 56°04'39.26"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-01-15 : 09:59:59

Still won't work. if i execut e it and add 3 Bay as the parameter it doesn;t return any records. Do you think it's the function ? In fact your sp I amended was the same as the last one you posted, but I copied and pasted yours incase I'd made an error.

set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[fnParseList]
(
@Delimiter CHAR,
@Text TEXT
)
RETURNS @Result TABLE (RowID SMALLINT IDENTITY(1, 1) PRIMARY KEY, Data VARCHAR(8000))
AS

BEGIN
DECLARE @NextPos INT,
@LastPos INT

SELECT @NextPos = CHARINDEX(@Delimiter, @Text, 1),
@LastPos = 0

WHILE @NextPos > 0
BEGIN
INSERT @Result
(
Data
)
SELECT SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)

SELECT @LastPos = @NextPos,
@NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1)
END

IF SCOPE_IDENTITY() > 0
INSERT @Result
(
Data
)
SELECT SUBSTRING(@Text, @LastPos + 1, DATALENGTH(@Text) - @LastPos)

RETURN
END

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-01-15 : 10:52:44

I changed to one of the other versions of fnParseList and it works. Thanks for your help and patience

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources