asked on

Help Using REGEX to Replace underscore with a space

i

I am querying my full text index using the contains in the select statement but I'm missing some data because the products are joined by _ (underscore)

I can get the data no problem from the other table columns but some of our suppliers only have the full make and model number of the product in the deep_link

The field that holds the data is the deep_link this is a url and I'm trying to pull back the data if it has the value

here is an example

i am searching for: ZKC6040X

the deep link value is

product.aspx?ProductID=7777&ProductCode=ZKC6040X_SS&my_source

because the the productcode has _SS at the end of it, the select contains statement doesnt find it.

So I'm looking for a way to replace the underscore _ with a space

not sure how to achieve this,

select statment below

SELECT dbid, merchant_name, merchant_id,product_name,replace(deep_link,'_','')as newdeeplink, deep_link, model_number,aw_thumb_url, description FROM awproductfeed WHERE (CONTAINS(product_name, '""" + Request("customs") + """') OR CONTAINS(model_number, '""" + Request("customs") + """') OR CONTAINS (deep_link,'""" + Request("customs") + """') OR CONTAINS(brand_name, '""" + Request("customs") + """') OR CONTAINS(description, '""" + Request("customs") + """') OR CONTAINS(specifications, '""" + Request("customs") + """') )"

Open in new window

Terry Woods

You seem to be replacing the _ with an empty string. Can you just replace it with a space instead?

SELECT dbid, merchant_name, merchant_id,product_name,replace(deep_link,'_',' ')as newdeeplink, deep_link, model_number,aw_thumb_url, description
FROM awproductfeed 
WHERE (CONTAINS(product_name, '""" + Request("customs") + """') OR CONTAINS(model_number, '""" + Request("customs") + """') OR CONTAINS (deep_link,'""" + Request("customs") + """') OR CONTAINS(brand_name, '""" + Request("customs") + """') OR CONTAINS(description, '""" + Request("customs") + """') OR CONTAINS(specifications, '""" + Request("customs") + """') )"

Open in new window

sparky74

ASKER

hi

yes i tried to do replace(deep_link,'_',' ')as newdeeplink, but then I couldnt seem to get that to work in

CONTAINS (deep_link,'""" + Request("customs") + """')

i guess if there is a space instead of the _ then the Select Contains Statement would find the text

can you show me how please?

Terry Woods

What database and languages are you working with?

sparky74

ASKER

ms sql server 2000

this is classic asp

Terry Woods

It might be worth using the Request Attention link to get those zones added to your question - Neither of those systems are particularly familiar to me, but that wasn't obvious when I looked at the question zone & question wording.

sparky74

ASKER

ok, thank you for your time.

Terry Woods

I may be able to help if you post the SQL as it is sent to SQL Server, rather than the copy and paste of ASP code - I'm not 100% what's going on with the quoting in ASP, though I assume it's correctly done.

I wonder if you can use LIKE instead of CONTAINS? Special characters for LIKE are % and _ (and probably [ and ] ) though, so any of those present in your data (and we know there's a _ character) will need to be escaped. There's some notes on escaping here: http://msdn.microsoft.com/en-us/library/ms179859.aspx

Here's my attempt, without the escaping done:

SELECT dbid,
       merchant_name, 
       merchant_id,
       product_name,
       replace(deep_link,'_','')as newdeeplink, 
       deep_link, 
       model_number,
       aw_thumb_url, 
       description
FROM awproductfeed 
WHERE (product_name LIKE '%" + Request("customs") + "%')
       OR model_number LIKE '%" + Request("customs") + "%') 
       OR deep_link LIKE '%" + Request("customs") + "%') 
       OR brand_name LIKE '%" + Request("customs") + "%') 
       OR description LIKE '%" + Request("customs") + "%') 
       OR specifications LIKE '%" + Request("customs") + "%')
      )

Open in new window

Terry Woods

Apologies, brackets were not correct:

SELECT dbid,
       merchant_name, 
       merchant_id,
       product_name,
       replace(deep_link,'_','')as newdeeplink, 
       deep_link, 
       model_number,
       aw_thumb_url, 
       description
FROM awproductfeed 
WHERE (product_name LIKE '%" + Request("customs") + "%'
       OR model_number LIKE '%" + Request("customs") + "%'
       OR deep_link LIKE '%" + Request("customs") + "%'
       OR brand_name LIKE '%" + Request("customs") + "%'
       OR description LIKE '%" + Request("customs") + "%'
       OR specifications LIKE '%" + Request("customs") + "%'
      )

Open in new window

ASKER CERTIFIED SOLUTION

Terry Woods

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sparky74

ASKER

Thanks you got me on the right path. I used this

OR (deep_link LIKE '%" + Request("customs") + "%'ESCAPE '_')

and it seems to be working..

Thank you for your time