By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,842 Members | 2,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,842 IT Pros & Developers. It's quick & easy.

Removing Non Alpha Characters before saving?

P: 17
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!
Nov 14 '08 #1
Share this Question
Share on Google+
8 Replies


Atli
Expert 5K+
P: 5,058
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT fields FROM table WHERE field LIKE 'Swine%Man'
  2.  
Nov 16 '08 #2

P: 17
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.
Nov 18 '08 #3

10K+
P: 13,264
1.) Are you using MySQL or MSSQL?
2.) What did you say is the reason for the SEARCHFIELD column?
Nov 18 '08 #4

P: 17
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.
Nov 19 '08 #5

10K+
P: 13,264
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.
Nov 20 '08 #6

P: 17
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!
Nov 20 '08 #7

10K+
P: 13,264
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?
Nov 20 '08 #8

ck9663
Expert 2.5K+
P: 2,878
It would be easier to parse the string on the front-end.

-- CK
Nov 21 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.