谁能教我做这个sql query呢？

一只优雅的猪

我的database，table : customer_profile 有一个record as below

customer_name = "jordan"
address = "34A, Jalan Sungai Long, Taman Universiti, 68000 Kajang"

/////////////////////////////////////////

那么我该如何写我的query才能达至即使我type [Universiti] or [Sungai Long]在我的 [keyword],也能search到我这个资料呢？

SELECT customer_name FROM customer_profile WHERE address = '" +keyword+ "'

DRAGONISM

SELECT customer_name FROM customer_profile WHERE address = '%" + keyword + "%'

一只优雅的猪

应该是 SELECT * FROM customer WHERE address LIKE '%" + keyword +"%'

谢了

[ 本帖最后由 一只优雅的猪 于 7-11-2005 02:10 PM 编辑 ]

goatstudio

应该这样写:

SELECT customer_name FROM customer_profile WHERE address LIKE '%" + keyword + "%'

shinelynn

可是要小心假如keyword有“‘”

SELECT CUSTOMER_NAME FROM CUSTOMER_PROFILE WHERE ADDRESS LIKE '%123A'BC%'
<--会有问题吗？

就算是普通的，
SELECT CUSTOMER_NAME FROM CUSTOMER_PROFILE WHERE ADDRESS='123A'BC' <--会
有问题吗？

通常你们是怎么做的？

goatstudio

原帖由 shinelynn 于 12-11-2005 01:41 AM 发表
可是要小心假如keyword有“‘”

SELECT CUSTOMER_NAME FROM CUSTOMER_PROFILE WHERE ADDRESS LIKE '%123A'BC%'
<--会有问题吗？

就算是普通的，
SELECT CUSTOMER_NAME FROM CUSTOMER_PROFILE WHERE A ...

SELECT CUSTOMER_NAME FROM CUSTOMER_PROFILE WHERE ADDRESS LIKE '%123A'BC%'
这样肯定会有问题.

解决方法很简单, 就是用 coding 里的任何一个 replace 方法. 把 " ' " replace 成 " '' " 就可以了.

ASP 的例子:


sql = "SELECT CUSTOMER_NAME FROM CUSTOMER_PROFILE WHERE ADDRESS LIKE '%" & Replace("123A'BC", "'", "''") & "%'"