July 16, 2015 at 9:04 am
Hello,
I have created the below function and apply it on a column in a table to replace the below identified values with Blank. It works fine but i have so many different varieties of values i need to add to ths list. Is there any way i insert these values in a table and call the values from that table instead of writing separate SET Statements.
CREATE FUNCTION [dbo].[sv_ReplaceChar] (@badString varchar(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
SET @badString = replace(@badstring, 'DR ', '')
SET @badString = replace(@badstring, 'JR', '')
SET @badString = replace(@badstring, ' MD', '')
SET @badString = replace(@badstring, 'SR', '')
RETURN @badString
END
Thanks for your help.
July 16, 2015 at 9:24 am
pinky (7/16/2015)
Hello,I have created the below function and apply it on a column in a table to replace the below identified values with Blank. It works fine but i have so many different varieties of values i need to add to ths list. Is there any way i insert these values in a table and call the values from that table instead of writing separate SET Statements.
CREATE FUNCTION [dbo].[sv_ReplaceChar] (@badString varchar(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
SET @badString = replace(@badstring, 'DR ', '')
SET @badString = replace(@badstring, 'JR', '')
SET @badString = replace(@badstring, ' MD', '')
SET @badString = replace(@badstring, 'SR', '')
RETURN @badString
END
Thanks for your help.
How many values are you talking about? I would suggest using a nested replace instead of using SET over and over. If you keep it to a single select statement you could also convert to an inline table valued function and gain some huge performance benefits too.
_______________________________________________________________
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 16, 2015 at 9:30 am
There may be more than 100 list of values that i may need to replace. I am reluctant to use Nested replace as its a big list. I was thinking if its table driven i can keep adding values in a table instead of using separate replace function.
July 16, 2015 at 9:54 am
pinky (7/16/2015)
There may be more than 100 list of values that i may need to replace. I am reluctant to use Nested replace as its a big list. I was thinking if its table driven i can keep adding values in a table instead of using separate replace function.
Yucko. Either way is going to be painful. The list of 100 nested replaces is unwieldy to say the least. The table approach forces you to loop which is inefficient.
_______________________________________________________________
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 16, 2015 at 9:55 am
This will be more complex than just what you've shown, if this is trying to strip prefixes/suffixes off a name, as it appears to be.
For example, the letters "JR" and "SR" could appear within a name: remember all the Polish, Czech and other nationalities you might encounter in naming. Thus, I would think you'd need to search for "[^a-z]DR|JR|MD|SR|..." or something along those lines.
[This is so involved it should really be done only on column insert or update: the match results and/or a stripped version of the name could be saved in a different column at that time.]
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply