[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 502
  • Last Modified:

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

0
sparky74
Asked:
sparky74
  • 6
  • 4
1 Solution
 
Terry WoodsIT GuruCommented:
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

0
 
sparky74Author Commented:
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?

0
 
Terry WoodsIT GuruCommented:
What database and languages are you working with?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
sparky74Author Commented:
ms sql server 2000

this is classic asp

0
 
Terry WoodsIT GuruCommented:
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.
0
 
sparky74Author Commented:
ok, thank you for your time.

0
 
Terry WoodsIT GuruCommented:
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

0
 
Terry WoodsIT GuruCommented:
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

0
 
Terry WoodsIT GuruCommented:
My intention was for the query to end up with the filter something like this:

WHERE (product_name LIKE '%ZKC6040X%'
...
0
 
sparky74Author Commented:
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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now