July 11, 2013 at 9:22 am
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 varchar(150))
RETURNS varchar(150)
AS
BEGIN
@strInputString = Replace((@strInputString), ", * *", "")
RETURN @strInputString
END
GO
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.
July 11, 2013 at 9:30 am
The problem is you're using double quotes instead of single quotes.
However, you should really avoid doing a simple replace with a UDF as it will degrade performance in a horrible way.
Why can't you ise the following? How dirty is the data you're receiving? With more details we could offer better help.
SELECT REPLACE(FieldName, ', * *', ''),
Field2,
Field3
FROM SomeTable
July 11, 2013 at 9:36 am
You are also missing a SET or SELECT on the line with your replace.
set @strInputString = Replace((@strInputString), ', * *', '')
I agree with Luis that doing this in a scalar function is performance timebomb. With more details we can help you put together a solution will not only work but also do it FAST!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 11, 2013 at 9:37 am
Maybe you could use something like this. The CTE is just to have some sample data to test with.
WITH SampleData(String) AS(
SELECT 'Doe, John, H *^' UNION ALL
SELECT 'Doe, John, H, **' UNION ALL
SELECT 'Doe, John, H *¨#$'
)
SELECT LEFT( String, LEN(String) - PATINDEX( '%[A-Za-z]%', REVERSE(String)) + 1)
FROM SampleData
July 11, 2013 at 9:51 am
Good point. It's about 4000 records that would call this function.
The data is pretty dirty. I'm pulling from a mainframe and retrieving First Name, Middle Name and Last Name. 19 out 20 times, First and Middle Name are all crammed into Last Name. But not always. Since the mainframe requires an entry on First and Middle Name, the user (not us) will just type crap into the fields.
In any event, let me experiment with doing it directly in the SELECT Statement.
Thanks guys - you guys are great.
July 11, 2013 at 8:58 pm
You can also use a pattern splitting FUNCTION like below:
WITH SampleData(String) AS(
SELECT 'Doe, John, H *^' UNION ALL
SELECT 'Doe, John, H, **' UNION ALL
SELECT 'Doe, John, H *¨#$')
SELECT String=Item
FROM SampleData
CROSS APPLY dbo.PatternSplitCM(String, '[A-Za-z ,]')
WHERE [Matched]=1
That FUNCTION can be found in the 4th article in my signature links.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply