Removing Non Alpha Characters before saving?

[Betray]

i have a table that lets say has 3 fields
ID, NAME, SEARCHFIELD
name can take any character, while searchfield only has the alpha/num characters from name. (eg name: Swine-?6$Man Searchfield:Swine6Man)

is there a way i could do this by just passing in the name to a stored procedure?

the reason im doing this, is if a user wants to search a database with only know the word/numerical values he knows. i couldnt think of another way, and im not good at sql. I was thinking of having it on the .net backend, but id like to keep on the DB side.

Thank you guys!

[Atli]

Hi.

You could use the LIKE clause to search using wild-cards.

For example, if you had a string like "Swine-?654Man", and you wanted a search for "Swine Man" to match that, you could simply convert the space into a wild-card (%) and execute a query like this:

SELECT fields FROM table WHERE field LIKE 'Swine%Man'
 

[Betray]

but see the poroblem with that is, when the user wants to do a search with out the fields it wont find it.
Database: Swine-?654Man
Users Search: SwineMan

it wont return any results. Unless there is a way to ignore characters in a MSSQL search.


For a temporary soloution, i created another field that has the user name with out any special characters. and thats what it searches against.
Original Field: Swine-?654Man
Searchable Field: SwineMan

Redundent data, i know. But i cant think of another soloution at this time.

[r035198x]

1.) Are you using MySQL or MSSQL?
2.) What did you say is the reason for the SEARCHFIELD column?

[Betray]

im using mssql, i created that field, so there is a searchable field
username: Froggy^Man
Search field: FroggyMan

Users Search: Froggy^Man

what i do is ignore special characters, and search the searchfield for the match.

[r035198x]

im using mssql, i created that field, so there is a searchable field
username: Froggy^Man
Search field: FroggyMan

Users Search: Froggy^Man

what i do is ignore special characters, and search the searchfield for the match.

Well you posted this in MySQL != MSSQL.
I have to say this special characters business is all very strange to me. What do you need those special characters for and where are they coming from.

P.S Moved to MSSQL forum.

[Betray]

Well you posted this in MySQL != MSSQL.
I have to say this special characters business is all very strange to me. What do you need those special characters for and where are they coming from.

P.S Moved to MSSQL forum.

thank you for moving it! Sorry about the confusion.

The Special characters are used for a product key. I guess i should explained it that way so there is a better understanding.
so lets say here is a product code: /HR/U39S03-33
now if a user wanted to do a search for that product id, but he only had the alpha/numeric characters, it would never pull it up. "HRU39S0333", so i created that search field, so that it only hold HRU39S0333. and if a user did a partial search, HRU39, it would find it, becauise i have LIKE %usersinput%. id like to get rid of that extra field, and extra data, and something less maintenance.
if there was some kinda of MSSQL command that could ignore those characters when doing the select statement with the "like" result.

hope this is a better description. Thank you pros!

[r035198x]

Couldn't you could still just use one field to store the code with those characters and then call some replace function on the database value when comparing with the user's input?

[ck9663]

It would be easier to parse the string on the front-end.

-- CK