SELECT Statement - Strip Non-Alpha/Numeric Chars

Results 1 to 2 of 2

Thread: SELECT Statement - Strip Non-Alpha/Numeric Chars

  1. #1
    Join Date
    Dec 1969

    Default SELECT Statement - Strip Non-Alpha/Numeric Chars

    I need to write a select statement that will remove all non-alpha/non-numeric characters from a field value. For example: If I was searching a StoreName field that contained a value "B.B.D&#039s Cafe" the result of this function would be "BBDsCafe" ... can somebody please tell me how I can do this? I would like to just be able to do something like:<BR><BR>SELECT * FROM datStores WHERE RemoveNons(StoreName) LIKE &#039%BBD%&#039<BR><BR>This way if somebody types in BBD, a record with a StoreName value of "B.B.D&#039s Cafe" will come up. Any help would be appreciated. Please do not respond by just saying to write a stored procedure, because if I knew how to write one to do this, I wouldn&#039t be asking. Thank you in advance.<BR><BR>Shawn Berg<BR>Innovative WebMedia

  2. #2
    Join Date
    Dec 1969

    Default Maybe try another approach?

    If you are using Access, you *could* do something like this:<BR><BR>SELECT * FROM datStores <BR>WHERE Replace( Replace( Replace(StoreName,".",""),"&#039&#039","")," ","") <BR>LIKE &#039%BBD%&#039<BR><BR>Do you see it? You nest the replaces, replacing one character at each level.<BR><BR>But that&#039s going to be pretty tedious (how many replaces will you need???) and will make the query slow.<BR><BR>I think a better idea would be to add another field to the table and put the "RemoveNons" version of the StoreName field into it. That way you do it one time, in a big DO WHILE NOT RS.EOF loop, adding in the new field as you go (via an UPDATE SQL statement, of course).<BR><BR>After that, whenever you add a new record to the table, you also add the "condensed" version of the StoreName field.<BR><BR>This is going to give you the best possible performance for something like you are doing. Almost surely better than even a stored procedure would do.<BR><BR>What do you think?<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts