Calling selected data in a string

[A1Pat]

Hi all,

I'm doing a quick search in a table and I'm having a problem specifying my data to the table's column's string.

for example:
SELECT * FROM tableA WHERE columnA LIKE '%4%'

Problem is the columnA carries string such as 1,3,14,20, and as you can see, this row is selected as LIKE '%4%' even it doesn't have 4 in its string.

Is there a better way to do this?

Thanks!

 

[guelphdad]

Yes it does, there is a 4 in 14 so therefore your query matches. the % matches any character not just a space.

Redesign your table so you don't have multiple values in a single column.

 

[A1Pat]

Thank guelphdad,

that could be a very good idea to redesign the table for my special need. however, I can't because such field was designed for its own special need as well.

What I've done right now is to call the table, run a DO WHILE loop to get each line, separate the values in the string, then verify them. If matched, the loop will go around again to display the line(s) that found, ELSE, display message "not found".

It seem to be a slow code, that's why I need a better way to do this.

 

[TonyGroves]

You could use:
[tt]
WHERE CONCAT(',',columna,',') LIKE '%,4,%'
[/tt]
This assumes there are no spaces in the field. And it won't be fast, as it won't use any indexes.

 

[A1Pat]

thanks tonyGroves,

I tried and got this message:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]'CONCAT' is not a recognized function name.

 

[guelphdad]

It helps if you post in the correct forum then. You have posted specifically in the MySQL forum and have been given a solutioon for that database.

 

[A1Pat]

guelphdad,

I'd thought about posting my question in the MySQL forum and posting my question as ASP coding instead. But I realized that I will have to go through that many steps UNLESS there is a way to specify right at the beginning, that is while calling database's table. If it works, I just shortcut 50% of the work to verify each line of the field. That is my goal.

Thanks!

 

[guelphdad]

The error you are getting is for SQL Server so are you using SQL Server or MySQL as your database? If the latter, your question belongs here, if the former it does not.

 

[TonyGroves]

If CONCAT doesn't work, then just use whatever string concatentation function/operator is available for your database.

 

[A1Pat]

to guelphdad,
Actually, I'm using SQL Server 2000. If my question is not apply to this forum, my apology.

to TonyGroves,
thanks!!!