April 13, 2017 at 2:27 am
hi.. how can i call back the function dynamically?
i create a function.
ALTER FUNCTION [dbo].[test] (@input nvarchar(max))
RETURNS VARCHAR(250)
AS BEGIN
DECLARE @check1 nvarchar(max),
@check2 nvarchar(max)
SET @check1 = 'mary'
SET @check2 = 'almond'
SET @check1 = @Input
set @check2 = @input
RETURN @check1
return @check2
END
-----------------------------
how can i call back the function if found mary trim it away. else just leave it.
by doing this way, is manual way.
select REPLACE(firstname, 'mary' ,'')
how to call back the function without putting mary?
April 13, 2017 at 3:17 am
I'm afraid I don't understand. Here's a guess, though: alter your function like this:
ALTER FUNCTION [dbo].[test] (@input nvarchar(max))
RETURNS VARCHAR(250)
AS BEGIN
DECLARE @check1 nvarchar(max),
@check2 nvarchar(max)
SET @check1 = 'mary'
SET @check2 = 'almond'
SET @check1 = REPLACE(@Input,'mary','')
set @check2 = @input
RETURN @check1
return @check2
GO
SELECT dbo.test(firstname)
FROM MyTable
Scalar functions aren't good for performance where you have a large result set. Consider converting your function to an inline table-valued function instead,
John
April 13, 2017 at 5:39 am
I'm a little confused by this function. You appear to be attempting to return two values.
What are you wanting to achieve with this function?
Thomas Rushton
blog: https://thelonedba.wordpress.com
April 13, 2017 at 8:20 am
girl_bj - Thursday, April 13, 2017 2:27 AMhi.. how can i call back the function dynamically?i create a function.
ALTER FUNCTION [dbo].[test] (@input nvarchar(max))
RETURNS VARCHAR(250)
AS BEGIN
DECLARE @check1 nvarchar(max),
@check2 nvarchar(max)SET @check1 = 'mary'
SET @check2 = 'almond'
SET @check1 = @Input
set @check2 = @inputRETURN @check1
return @check2
END-----------------------------
how can i call back the function if found mary trim it away. else just leave it.
by doing this way, is manual way.
select REPLACE(firstname, 'mary' ,'')how to call back the function without putting mary?
Part of the problem here is thinking that using a user-defined function to replace a string value is going to help solve a problem. The function you describe does nothing but echo back the inputs, which serves no useful purpose, unless you count wasting cpu cycles as "useful". This forces me to believe that something else is the issue, but you don't quite know how to abstract the problem into simpler terms. Please provide more detail on what your overall objective is. If you just need to replace a string, the REPLACE function is better than using a scalar udf to do the same thing, because then you don't need the overhead of calling the udf. Mind you, REPLACE is a relatively expensive string operation from a cpu usage perspective, and for large data sets, can slow down the query considerably, but adding the overhead of a scalar udf to boot isn't going to help.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 13, 2017 at 8:41 am
i would like to clean it on certain condition.
if user input name which contains Mary or John or Calley and few more conditions, i would like to delete it.
declare @name nvarchar(100)
set @name = 'Mary Jane'
if user input 'Mary Jane' return as only Jane.
if user input just 'Jane' return Jane.
Can you advice where shall i apply the few conditions.
April 13, 2017 at 10:43 am
Okay, that provides the basic logic you're seeking to follow, but what you haven't described is the scope of the problem, meaning is this for a small fixed number of values on a one-time basis, or are you looking to "filter" values that exist in a table? And does this process need to be a one-time effort or something repeatable that runs on a regular basis?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 13, 2017 at 10:46 am
And perhaps more importantly, WHY are you needing to filter out certain values, and are you sure that you will always want to filter out such values? Are there conditions, say a name like Rosemary, where you would NOT want to remove the "mary" portion?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 13, 2017 at 10:51 am
girl_bj - Thursday, April 13, 2017 8:41 AMi would like to clean it on certain condition.
if user input name which contains Mary or John or Calley and few more conditions, i would like to delete it.declare @name nvarchar(100)
set @name = 'Mary Jane'if user input 'Mary Jane' return as only Jane.
if user input just 'Jane' return Jane.Can you advice where shall i apply the few conditions.
Here's an example on how to create the function and how to use it:
CREATE FUNCTION [dbo].[CleanName] (
@input nvarchar(250)
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
SELECT LTRIM( REPLACE( REPLACE( REPLACE( @input, 'Mary', ''), 'John', ''), 'Calley', '')) CleanName;
GO
DECLARE @Sample TABLE(
name varchar(250));
INSERT INTO @Sample
VALUES( 'Mary Jane'), ('Jane'), ( 'John'), ('John Paul'), ('Luis');
SELECT *
FROM @Sample s
CROSS APPLY dbo.CleanName( s.name) cn;
GO
DROP FUNCTION CleanName;
April 13, 2017 at 6:07 pm
sgmunson - Thursday, April 13, 2017 10:46 AMAnd perhaps more importantly, WHY are you needing to filter out certain values, and are you sure that you will always want to filter out such values? Are there conditions, say a name like Rosemary, where you would NOT want to remove the "mary" portion?
yes as long as theres the name condition exist i would like to clean it. is that possible?
April 14, 2017 at 10:24 am
girl_bj - Thursday, April 13, 2017 6:07 PMsgmunson - Thursday, April 13, 2017 10:46 AMAnd perhaps more importantly, WHY are you needing to filter out certain values, and are you sure that you will always want to filter out such values? Are there conditions, say a name like Rosemary, where you would NOT want to remove the "mary" portion?yes as long as theres the name condition exist i would like to clean it. is that possible?
Sure, it's possible, and Luis has already posted the how, but I still have to ask why... You would appear to be willing to change the data for someone's name without any concern for maintaining accurate information, and that's just not usually a good idea. If someone's name in the system was Rosemary, and you just take out the "mary" part and now it's just "Rose", couldn't Rosemary have a good reason to not like that change? If you can explain the why, maybe it would be easier to understand your motivation...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply