Search for * 1

[csk003]

How can I search for character * through a query in a column's data?

 

[lespaul]

maybe:

SELECT * FROM TableName WHERE FieldName LIKE '***'

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

One way:
WHERE FieldName LIKE '*[*]*'

Another way:
WHERE InStr(FieldName, '*') > 0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

I see, need the brackets for the *, interesting.....learn something new everyday.

Thanks!
Les

 

[PHV]

need the brackets for the *
Yes, JetSQL lacks the Escape character ...

 

[csk003]

Thanks PHV.
I am trying to replace this * with null and I am using this query
update test1 set column1 = replace(column1,'*[*]*' ,"",1)
Nothing gets replaced. Any ideas?

 

[lespaul]

I think the replace function is looking to replace
*[*]*, which it is not finding. Have you tried:
[tt]
replace(column1, "*", "")[/tt]

 

[PHV]

Furthermore, to avoid unnecessary updates:
UPDATE test1
SET column1 = Replace([column1], '*', '')
WHERE column1 Like '*[*]*'


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[csk003]

Thak you Paul. I was thinking it would replace all characters if use "*" as it is wildcard but as we are using in " should be fine.

 

[lespaul]

No problem!

les

 

[csk003]

Thanks PHV. I am glad the query now prompts that it only updates how many ever rows that had * now instead of all the rows that the table has (it was giving this message earlier which got me confused)

 

[PHV]

For me it's a good habbit to always use a WHERE clause in an UPDATE or DELETE instruction ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886