Problem when creating query on base of combination

Rupesh Kumar Swami

hi all i want to prepare a query which returns the result on the basis of combination of character. In my MSAccess Database, table have two fields Id and Name Scenario is following 1 have 3 buttons with title "abc" ,"def","ghi" 1.if we first click on "abc" button then query must return the result for which name field begins with 'a' or 'b' or 'c' OR it contains " a"(space before a) or " b"(space before b) or " c"(space before c). 2. Now if we click on button "def" (remember that in previous step we click on "abc" button) then it must return the record that start with ('ad','bd','cd','ae','be','ce','af','bf','cf') OR it contains (' ad',' bd',' cd',' ae',' be',' ce',' af',' bf',' cf') means space before text and so on.. Please suggest me how can i build query for this type of condition.

Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) My Company Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

Syed Mehroz Alam

Hi, A complex problem, but perhaps this[^] thread might be useful. Regards, Syed Mehroz Alam.

My Blog My Articles Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein

Rupesh Kumar Swami

Thanks syed, but it does not solve my problem

Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) My Company Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

Niladri_Biswas

Dear Rupesh, As I don't have MS Access in my system. So I was forced to solve the problem in SQL Server 2005. But the syntax will remain same in both the cases(or may be minor changes which I hope you can do). I have created a table[TBLSEARCH] with the following values

a
b
c
a
b
c
AC
AD
AD
AE
AE
AF
AF
AFGTHY
BE
BE

And my Stored Proc goes like this

**ALTER PROCEDURE sp_Search
-- Add the parameters for the stored procedure here
(@SEARCHSTRING VARCHAR(50))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- SEARCH WITH ABC COMBINATION
IF(UPPER(@SEARCHSTRING) = 'ABC')

	BEGIN
		
		SELECT \* 
		FROM TBLSEARCH WHERE RTRIM(LTRIM(NAME)) LIKE '\[A-C\]%'

	END

-- SEARCH WITH DEF COMBINATION
IF(UPPER(@SEARCHSTRING) = 'DEF')

	BEGIN
		
		SELECT \* 
		FROM TBLSEARCH WHERE 

			RTRIM(LTRIM(NAME)) LIKE 'A\[DEF\]%' OR

			RTRIM(LTRIM(NAME)) LIKE 'B\[DEF\]%' OR

			RTRIM(LTRIM(NAME)) LIKE 'C\[DEF\]%'

	END

-- SEARCH WITH GHI COMBINATION
IF(UPPER(@SEARCHSTRING) = 'GHI')

	BEGIN
		
		SELECT \* 
		FROM TBLSEARCH WHERE 

			RTRIM(LTRIM(NAME)) LIKE 'A\[DEF\]\[GHI\]%' OR

			RTRIM(LTRIM(NAME)) LIKE 'B\[DEF\]\[GHI\]%' OR

			RTRIM(LTRIM(NAME)) LIKE 'C\[DEF\]\[GHI\]%'

	END

END
GO**

N.B.~ The @SEARCHSTRING can assume values either 'ABC' or 'DEF' or 'GHI' [ As your button names ] In my system I am getting the correct output. Please check it and let me know if it is working as per your expectation or not. Regards, Niladri Biswas :)

Niladri Biswas

modified on Wednesday, June 24, 2009 8:40 AM

J4amieC · modified on Wednesday, June 24, 2009 8:40 AM

You can do letter groupings in a LIKE clause using SQL2005? cool!

Niladri_Biswas

Yes it is possible. For more details visit this site http://msdn.microsoft.com/en-us/library/aa933232(SQL.80).aspx[^] :)

Niladri Biswas

Rupesh Kumar Swami · modified on Wednesday, June 24, 2009 8:40 AM

first of all many many thanks. 5 from me and sorry for late reply since i am busy with some other project.so i do not reply you This query works well. one more thing whether you have any idea of SQLite. since i just check and run this query in sqlite. but i don't know the syntax which is similar to above syntax (as you suggest)

Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) My Company Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11