January 15, 2014 at 8:01 am
I have an function which return variable by replacing strings which i will be passing in my function
´==========================
TABLE DATA
´==========================
Branch is not in Chicago and Branch is not in Newyork and Branch is not in Dehil
Branch is in Chicago and Branch is in Mexico
Branch is not in Delhi
Branch is in Stockholm
i need to find
´==========================
EXISTING FUNCTION
´==========================
CREATE FUNCTION [dbo].[GetRelevantCity]
(
@GetRelevantCity varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
DECLARE @strVariantCondition varchar(max)
set @strVariantCondition = ''
if @GetRelevantCity = ''
return @strVariantCondition
BEGIN
set @strVariantCondition = LTRIM(RTRIM(CAST(@GetRelevantCity as varchar(max))))
If @strVariantCondition <> '' set @strVariantCondition = 'City is ' + @strVariantCondition
END
RETURN @strVariantCondition
END
´==========================
if the string has 'Branch is in' and replace with ,
if the string has 'Branch is not in' and replace with ,
I expect to get the result as below in my function.
´==========================
My Function should return
´==========================
Branch is not in Chicago , Newyork , Dehil
Branch is in Chicago , Mexico
Branch is not in Delhi
Branch is in Stockholm
Kindly suggest me the changes to do
January 15, 2014 at 8:29 am
I was thinking this could be a complex split and join strings based on patterns, but I found a possible better way which you can transform into an inLine Table-Valued Function to gain performance if you really need the function.
Note that I included DDL and sample data in a way that only needs to be copied and pasted on SSMS to avoid wasting time on creating it. Your expected to do the same to get better and faster replies. 😉
CREATE TABLE #SampleData(
SomeText varchar(1000))
INSERT #SampleData SELECT
'Branch is not in Chicago and Branch is not in Newyork and Branch is not in Dehil' UNION ALL SELECT
'Branch is in Chicago and Branch is in Mexico' UNION ALL SELECT
'Branch is not in Delhi' UNION ALL SELECT
'Branch is in Stockholm';
SELECT SomeText,
CASE WHEN SomeText LIKE 'Branch is in%'
THEN 'Branch is in '
ELSE 'Branch is not in ' END +
REPLACE( REPLACE( REPLACE(SomeText, 'Branch is in ', ''), 'Branch is not in ',''), ' and ', ',')
FROM #SampleData
DROP TABLe #SampleData
January 15, 2014 at 8:31 am
if the string has 'Branch is in' and replace with ,
if the string has 'Branch is not in' and replace with ,
I expect to get the result as below in my function.
´==========================
My Function should return
´==========================
Branch is not in Chicago , Newyork , Dehil
Branch is in Chicago , Mexico
Branch is not in Delhi
Branch is in Stockholm
With what you are asking for you will be back the following:
, Chicago , Newyork , Dehil
, Chicago , Mexico ... etc
I think what you want to replace is "and Branch is [not] in" string.
What is the purpose of the function? Is this to clean data after it has been inserted? Is this for return a better looking string?
Depending on the amount of data that you have and the type of query you are using this function it may have performance issues.
January 15, 2014 at 8:48 am
Thanks Luis for the code.
How to send them to different column ?
Like,
select dbo.GetRelevantCity(ColumnA , 'BI') as BranchIn , dbo.GetRelevantCity(ColumnA, 'BN') as BranchNotIn ,
dbo.GetRelevantCity(ColumnA, 'BN') as OtherInfo from table
there will be also data of other information like
'( Location is NorthCity or Location is Downtown) and Branch is Delhi '
´´´´´´´´´´´´´´´´´´
Expected Result
´´´´´´´´´´´´´´´´´´
BranchIn , BranchNotIn, OtherInfo
Delhi, , Location is NorthCity or Location is Downtown
ALTER FUNCTION [dbo].[GetRelevantCity]
(
@GetRelevantCity varchar(max) ,
@ColParam char(2)
)
RETURNS varchar(max)
AS
BEGIN
DECLARE @strVariantCondition varchar(max)
set @strVariantCondition = ''
if @GetRelevantCity = ''
return @strVariantCondition
if @ColParam ='BI'
BEGIN
set @strVariantCondition = LTRIM(RTRIM(CAST(@strVariantCondition as varchar(max))))
If @strVariantCondition <> '' set @strVariantCondition = 'Branch is ' + @strVariantCondition
END
else if @ColParam ='BN'
BEGIN
set @strVariantCondition = LTRIM(RTRIM(CAST(@strVariantCondition as varchar(max))))
set @strVariantCondition = REPLACE( substring(@strVariantCondition,charindex('country is ',@strVariantCondition),len(@strVariantCondition)),')','')
If @strVariantCondition <> '' set @strVariantCondition = 'Branch is not ' + @strVariantCondition
END
else
BEGIN
set @strVariantCondition = 'Other ' + LTRIM(RTRIM(CAST(@strVariantCondition as varchar(max))))
END
RETURN @strVariantCondition
END
January 15, 2014 at 9:05 am
I'm not sure what you need.
If your strings have different structures, you might be in serious trouble.
Your function might cause serious performance troubles. As I told you before, you should try to convert it to an inline table valued function. http://qa.sqlservercentral.com/articles/T-SQL/91724/
January 15, 2014 at 2:56 pm
CREATE TABLE #SampleData(
SomeText varchar(1000))
INSERT #SampleData SELECT
'( Branch is not CA and Branch is not MX)' UNION ALL SELECT
'( Branch is not CA and Branch is not MX and Branch is not US)' UNION ALL SELECT
'( Location North City) and ( Branch is not CA and Branch is not MX and Branch is not US)' UNION ALL SELECT
'( Branch is not CA and Branch is not MX) and ( Location North City) ' UNION ALL SELECT
'( Branch is not CA and Branch is not MX and Branch is not US) and ( MoreSales is Black or MoreSales is Red)' UNION ALL SELECT
'( MoreSales is Black or MoreSales is Red) and ( Location North City or Location Down Town )';
select SomeText from #SampleData
declare @ch varchar(200)
declare @result varchar(200)
--set @ch = '( Branch is not CA and Branch is not MX)'
--set @ch = '( Branch is not CA and Branch is not MX and Branch is not US)'
--set @ch = '( Location North City)'
--set @ch = '( Location DownTown) and ( Branch is not CA and Branch is not MX and Branch is not US)'
--set @ch = '( Branch is not CA and Branch is not MX) and ( Location North City) '
set @ch = '( Branch is not CA and Branch is not MX and Branch is not US) and ( MoreSales is Black or MoreSales is Red)'
set @ch = '( MoreSales is Black or MoreSales is Red) and (Location North City or Location Down Town )'
BEGIN
set @result = LTRIM(RTRIM(CAST(@ch as varchar(max))))
set @result = LEFT(@result,
CASE WHEN PATINDEX('%Branch is not %',@result) > 0
THEN PATINDEX('%Branch is not %',@result)
ELSE PATINDEX('%Branch is %',@result) END )
END
select @result
DROP TABLe #SampleData
===========================
EXPECTED RESULT --
--- Display '' If no data other than country info
--- Display full data If no country info
--- Remove the values starting between '( Country )'
--- Remove the unwanted 'and'
===========================
''
''
'( Location North City)'
'( Location DownTown)'
'(Location North City) '
'( MoreSales is Black or MoreSales is Red)'
'( MoreSales is Black or MoreSales is Red) and ( Location North City or Location Down Town )'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply