This should be a simple task but I think I'm tripping over the syntax. I have data from a database that is fed out onto a web page (aspx). Due to reasons out of my control, the data comes to me as "dirty". 99% but not 100% of the data comes with "junk" characters appended to the end of the string. So, full name might look like "Doe, John, H *^" where it should look like "Doe, John, H".
I could do this one the front end and rewrite some of the web page's logic but I'm trying to handle this on the back end. It would seem I could write a simple Function to "strip" out junk chars I see fit to remove. So, in my query, I could do something like:
SELECT dbo.MyCustomFunction(FieldName), Field2, Field3 FROM......etc...
Then, if I've written the function correct, it'll just strip out the bad chars. I've done this many times before with VB.
The Custom Function on SQL Server is where I'm having trouble. Here's what I have so far:
CREATE FUNCTION fncStripBadChars
@strInputString = Replace((@strInputString), ", * *", "")
It's the BOLDED line above is where I'm having a little trouble. I'm getting an incorrect Syntax error.
Any tips or pointers? See what I'm doing wrong?
I should also mention that the ",* *" is one of the few strings I'm trying to replace with an empty string.