CODEWITHSUNDEEP
sqlsql-serversql-server-2000
Gopal
Gopal

Reputation: 11982

How to search the column values

Table1

Values

9A 200x400x600
10B 400x200x500
....

Values Column datatype is nvarchar

Query

Select * from table1 where values like '" & textbox1.text & "%'

The above query is working, but I want to search in 4 ways

I want to search a column with every 3 digit

The query should not check space and x values from the column.

Expected Output

How to split the column value for search

Need query help

Upvotes: 1

Views: 120

Answers (3)

gngolakia
gngolakia

Reputation: 2216

Select " & textbox1.text & " as mYSearch,* 
from table1 
where values like '%" & textbox1.text & "%'

Upvotes: 0

dani herrera
dani herrera

Reputation: 51705

As you know the query is a the question is a bit confused. I will try, let me know if this is close that you are looking for.

I assume that:

9A 200x400x600  -> 200x400x600 are de 3*2+2 lasts characters.
10B 400x200x500 -> CHARINDEX(' ', value ) = 4

Then, the query is:

Select 
     * 
from table1 
where 
   substring( values, 1, CHARINDEX(' ', value ) ) = '" & textbox1.text & "' OR
   substring( right( value, 3*2+2), 1, 3 ) = '" & textbox2.text & "' OR
   substring( right( value, 3*2+2), 5, 3 ) = '" & textbox3.text & "' OR
   substring( right( value, 3*2+2), 9, 3 ) = '" & textbox4.text & "'

edited:

select * from
   (Select 
     substring( values, 1, CHARINDEX(' ', value ) ) as split1,
     substring( right( value, 3*2+2), 1, 3 ) as split2,
     substring( right( value, 3*2+2), 5, 3 ) as split3,
     substring( right( value, 3*2+2), 9, 3 ) as split4,
     * 
   from table1 ) SplitedTable
where 
   SplitedTable.split1 = '" & textbox1.text & "' OR
   SplitedTable.split2 = '" & textbox2.text & "' OR
   SplitedTable.split3 = '" & textbox3.text & "' OR
   SplitedTable.split4 = '" & textbox4.text & "'

Upvotes: 1

user359040
user359040

Reputation:

Please look into database normalisation, particularly first normal form.

At present, your data field values is being used to hold multiple different values in a single row - eg. 9A 200x400x600. (Not only that, but you are also using different value delimiters in the same field, as both and x are being used to delimit fields.)

SQL is not designed for this sort of manipulation.

While it would be possible to produce a query that would return the relevant values, such a query would be unwieldy and inflexible. I therefore suggest normalising the data structure, to be more suitable for querying.

Upvotes: 1

Related Questions