Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

A little help needed with simple Custom Function Expand / Collapse
Author
Message
Posted Thursday, July 11, 2013 9:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:46 AM
Points: 98, Visits: 241
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.

Post #1472648
Posted Thursday, July 11, 2013 9:30 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 3,545, Visits: 7,659
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




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1472655
Posted Thursday, July 11, 2013 9:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1472661
Posted Thursday, July 11, 2013 9:37 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 3,545, Visits: 7,659
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




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1472662
Posted Thursday, July 11, 2013 9:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:46 AM
Points: 98, Visits: 241
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.
Post #1472669
Posted Thursday, July 11, 2013 8:58 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:13 PM
Points: 3,627, Visits: 5,274
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1472846
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse