July 15, 2009 at 7:18 am
hi,...
How to search like this.
I give the text like :Good Morning
I want ot display Good data and Morning Data and Good Morning data.
It is posible to write query in sql.
Thanks
Dastagiri.D
July 15, 2009 at 7:45 am
Greetings,
I would think that what you want is possible to be able to search on any word in your search criteria. But, I suspect what you would need to do is to parse through your search string and break it into individual pieces based on a space. Then build that into a LIKE operator that OR's each piece.
WHERE
MyField LIKE '%Morning%'
OR MyField LIKE '%Good%'
The other option is to build each piece into a temporary table (# or @ types). Then you can join it back to your original table. You can make each search field in the table like what is above.
SELECT
*
FROM MyTable
JOIN @SearchList ON MyField LIKE SearchField
Both of these ways will work for you, but they do take a little bit of code, like a function or a stored procedure to make it work easier. I am sure that others will have bigger or better ideas for you as well.
Have a good day.
Terry Steadman
July 15, 2009 at 7:55 am
hi ..
I send that 'Good Morning' as parameter in sql.
how it display :Good and Morning and Good Morning Data.
Regards:
Giri
Thanks
Dastagiri.D
July 15, 2009 at 8:02 am
Hi Giri,
Your question is not clear.
How to search like this.
You are saying here you want to search
I give the text like :Good Morning
I want ot display Good data and Morning Data and Good Morning data.
Here you are saying you want to display
Do you want to display or do you want to search
[font="Verdana"]Thanks
Chandra Mohan[/font]
July 15, 2009 at 8:16 am
Hi,
I am sure that there are better / quicker and shorter ways of doing this, and ways that use only set based programming however i am not sure what they are!! i am sure someone will post one though. Until then, this should do what you require
If you turn this into a procedure that asks for a parameter then this will take the parameter, split it into seperate words in a temp table , then search on the specified table / column for anything matching those words.
Let me know what you think :
-- If the temporary account detail table already exists, drop it.
IF OBJECT_ID('TempDB..#tempa', 'U') IS NOT NULL
DROP TABLE #tempa
----- Create temporary table to store the results in. you will
----- need some sort of Id col or reference -----
----- For the results that have been found, and i am assuming you
----- want to see the results -----
CREATE TABLE #tempa (
idcol INT,
NAME VARCHAR(100)
)
------
DECLARE @Parameter VARCHAR(8000)
DECLARE @text VARCHAR(100)
----- This is your parameter that holds the word(s) you wish to search on
SET @Parameter = 'Good test'
--- Declare table and seperate the words into a table ---
DECLARE @Elements TABLE
(
Number INT IDENTITY(1,1), --Order it appears in original string
Value VARCHAR(8000) --The string value of the element
)
--===== Decalre a variable to remember the position of the current Space
DECLARE @N INT
--===== Add start and end spaces to the Parameter so we can handle -- single elements
SET @Parameter = ' '+@Parameter +' '
--===== Preassign the current comma as the first character
SET @N = 1
--===== Loop through and find each comma, then insert the string value
-- found between the current comma and the next comma. @N is
-- the position of the current space.
WHILE @N < LEN(@Parameter)
--Don't include the last space
BEGIN
--==== Do the insert using the value between the spaces
INSERT INTO @Elements
VALUES (SUBSTRING(@Parameter,@N+1,CHARINDEX(' ',@Parameter,@N+1)-@N-1))
--==== Find the next space
SELECT @N = CHARINDEX(' ',@Parameter,@N+1)
END
-- Loop through the search table and return all records that match the words specified --
DECLARE @idcol INT
DECLARE @MaxIDcol INT
DECLARE @SearchWord VARCHAR(20)
SET @idcol = 1
SET @MaxIDcol = (SELECT MAX(Number) FROM @Elements)
WHILE @idcol <= @MaxIDcol
BEGIN
SELECT @searchword = Value FROM @Elements WHERE Number = @idcol
INSERT INTO #tempa
SELECT idcolumn,NAME FROM sometable
WHERE NAME LIKE '%' + @searchword + '%'
SET @idcol = @idcol + 1
END
--- Return the table without duplicates ---
SELECT DISTINCT * FROM #tempa
ORDER BY [idcol]
Thanks
July 15, 2009 at 8:19 am
.....ignore the bits in the comments that say "comma". they are meant to say "space". I have stolen this code from somewhere else and missed a few bits.
This will work for any identifyer though so its pretty useful.
Thanks
July 15, 2009 at 9:44 pm
hi.
After search is over.The search result I need that what I have mentioned in above data.
Thanks
Dastagiri.D
July 15, 2009 at 9:54 pm
Sorry for this I was not able to under stand above code.
Mytable : Like this
ID | name
-------------------
1 | Good Morning
2 | Good
3 | Morning
I send parameter value form my c# code:@paramname='Good Moning'
CREATE PROC seach(@paramname nvarchar(50))
as
...what query i have written here when I get output Good Morning,Good,Morning three data need to display.
...
Regards:
Giri
Thanks
Dastagiri.D
July 16, 2009 at 1:29 am
--Hope this will solve your problem:
DECLARE @text varchar(50) = 'Good Morning SQL'
SET @text = @text+' '
DECLARE @Spacepos int = charindex(' ',@text)
DECLARE @Len int = LEN(@text)+1
DECLARE @Oldval int = 0
--Create a temp table
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp
(ID smallint identity(1,1),
Word varchar(50))
WHILE @Spacepos <=@Len
BEGIN
--Insert each word into #temp
INSERT INTO #temp
SELECT SUBSTRING(@text,@Oldval+1,(@Spacepos-@Oldval))
SET @Oldval = @Spacepos
IF @Spacepos = @Len
BREAK
SET @Spacepos = charindex(' ',@text,@Spacepos+1)
END
DECLARE @min-2 int
DECLARE @max-2 int
DECLARE @String varchar(50)
SELECT @min-2 = MIN(ID)FROM #temp
SELECT @max-2 = MAX(ID)FROM #temp
SELECT @String = Word FROM #temp WHERE ID = @min-2
IF OBJECT_ID('tempdb..#result') IS NOT NULL
DROP TABLE #result
CREATE TABLE #result
( Word varchar(50))
INSERT INTO #result SELECT Word FROM #temp
WHILE @min-2<@max
BEGIN
SELECT @String = @String + Word FROM #temp WHERE ID = @min-2+1
INSERT INTO #result SELECT @String
END
SELECT * FROM #result
July 16, 2009 at 1:55 am
HI,
If you can let me know what you dont understand about the code that i posted then i am happy to try and explain it.
Thanks
July 16, 2009 at 2:36 am
You will need to change the bits marked "--->>" so that it uses your table, and your column names. This will return the records as specified.
Let me know if it makes sense.
CREATE PROC seach(@ParamName nvarchar(50))
AS
-- If the temporary account detail table already exists, drop it.
IF OBJECT_ID('TempDB..#tempa', 'U') IS NOT NULL
DROP TABLE #tempa
----- Create temporary table to store the results in. you will need some sort of Id col or reference -----
----- For the results that have been found, and i am assuming you want to see the results -----
CREATE TABLE #tempa (
idcol INT,
NAME VARCHAR(100)
)
------
DECLARE @ParamName VARCHAR(8000)
DECLARE @text VARCHAR(100)
--- Declare table and seperate the words into a table ---
DECLARE @Elements TABLE
(
Number INT IDENTITY(1,1), --Order it appears in original string
Value VARCHAR(8000) --The string value of the element
)
--===== Decalre a variable to remember the position of the current Space
DECLARE @N INT
--===== Add start and end spaces to the Parameter so we can handle -- single elements
SET @ParamName = ' '+@ParamName +' '
--===== Preassign the current Space as the first character
SET @N = 1
--===== Loop through and find each Space, then insert the string value
-- found between the current Space and the next Space. @N is
-- the position of the current space.
WHILE @N < LEN(@ParamName)
--Don't include the last space
BEGIN
--==== insert the value between the spaces
INSERT INTO @Elements
VALUES (SUBSTRING(@ParamName,@N+1,CHARINDEX(' ',@ParamName,@N+1)-@N-1))
--==== Find the next space
SELECT @N = CHARINDEX(' ',@ParamName,@N+1)
END
-- Loop through the search table and return all records that match the words specified --
DECLARE @idcol INT
DECLARE @MaxIDcol INT
DECLARE @SearchWord VARCHAR(20)
SET @idcol = 1
SET @MaxIDcol = (SELECT MAX(Number) FROM @Elements)
WHILE @idcol > CHANGE TO COLUMN NAMES FROM YOUR TABLE
NAME -->> CHANGE TO COLUMN NAMES FROM YOUR TABLE
FROM yourtable --->>> YOU WILL NEED TO CHANGE "SOMETABLE" TO THE NAME OF THE TABLE HOLDING YOUR DATA
WHERE NAME LIKE '%' + @searchword + '%'
SET @idcol = @idcol + 1
END
--- Return the table without duplicates ---
SELECT DISTINCT * FROM #tempa
ORDER BY [idcol]
July 16, 2009 at 4:51 am
Hi.
Thank u for response
where cna i use my database table query to search .
Thanks
Dastagiri.D
July 16, 2009 at 6:01 am
Ohhhh!!!!!!!!!!
Excellent it is working.I was mistaked to impliment your code.
Its woking fine.
a small thing in it .
I give parameter :Goo
it display the good morning data and good data .
is there to display only 'Goo' data instead of that.
Thanks
Dastagiri.D
July 16, 2009 at 7:02 am
I have added a flag and put a default on it of "0" which means you only need to put a value into it if you want it to be 1.
If the flag is 0 then it will perform a like query returning all results that contain those words. If the flag is 1 then it will only return exact matches.
CREATE PROC search
@ParamName nvarchar(50),
@Flag INT = 0
AS
-- If the temporary account detail table already exists, drop it.
IF OBJECT_ID('TempDB..#tempa', 'U') IS NOT NULL
DROP TABLE #tempa
----- Create temporary table to store the results in. you will need some sort of Id col or reference -----
----- For the results that have been found, and i am assuming you want to see the results -----
CREATE TABLE #tempa (
idcol INT,
NAME VARCHAR(100)
)
--- Declare table and seperate the words into a table ---
DECLARE @Elements TABLE
(
Number INT IDENTITY(1,1), --Order it appears in original string
Value VARCHAR(8000) --The string value of the element
)
--===== Decalre a variable to remember the position of the current Space
DECLARE @N INT
--===== Add start and end spaces to the Parameter so we can handle -- single elements
SET @ParamName = ' '+@ParamName +' '
--===== Preassign the current Space as the first character
SET @N = 1
--===== Loop through and find each Space, then insert the string value
-- found between the current Space and the next Space. @N is
-- the position of the current space.
WHILE @N < LEN(@ParamName)
--Don't include the last space
BEGIN
--==== insert the value between the spaces
INSERT INTO @Elements
VALUES (SUBSTRING(@ParamName,@N+1,CHARINDEX(' ',@ParamName,@N+1)-@N-1))
--==== Find the next space
SELECT @N = CHARINDEX(' ',@ParamName,@N+1)
END
-- Loop through the search table and return all records that match the words specified --
DECLARE @idcol INT
DECLARE @MaxIDcol INT
DECLARE @SearchWord VARCHAR(20)
SET @idcol = 1
SET @MaxIDcol = (SELECT MAX(Number) FROM @Elements)
WHILE @idcol > CHANGE TO COLUMN NAMES FROM YOUR TABLE
[NAME] -->> CHANGE TO COLUMN NAMES FROM YOUR TABLE
FROM sometable --->>> YOU WILL NEED TO CHANGE "SOMETABLE" TO THE NAME OF THE TABLE HOLDING YOUR DATA
WHERE NAME LIKE '%' + @searchword + '%'
SET @idcol = @idcol + 1
END
IF @Flag = 1
BEGIN
SELECT @searchword = Value FROM @Elements WHERE Number = @idcol
INSERT INTO #tempa
SELECT
[ID], -->> CHANGE TO COLUMN NAMES FROM YOUR TABLE
[NAME] -->> CHANGE TO COLUMN NAMES FROM YOUR TABLE
FROM sometable --->>> YOU WILL NEED TO CHANGE "SOMETABLE" TO THE NAME OF THE TABLE HOLDING YOUR DATA
WHERE NAME = @searchword
SET @idcol = @idcol + 1
END
END
--- Return the table without duplicates ---
SELECT DISTINCT * FROM #tempa
ORDER BY [idcol]
/*
EXEC search 'test',0
*/
is this what you were after?
Thanks
July 16, 2009 at 7:22 am
Its working fine..
Really its helps me lot ...
Thanks...................
Thanks
Dastagiri.D
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply