Find char in function variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Find char in function variable

Author

Topic

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2014-01-15 : 11:01:08

I have an function which return variable by replacing strings which i will be passing in my function
COLUMNA
=========
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
( Location is NorthCity or Location is Downtown) and Branch is Delhi

CREAT 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

How to send them to different column ? My Query is ::
select dbo.GetRelevantCity(ColumnA , 'BI') as BranchIn , dbo.GetRelevantCity(ColumnA, 'BN') as BranchNotIn, dbo.GetRelevantCity(ColumnA, 'OT') as OtherInfo from Table

===================================
My Function should return RESULT
===================================
BranchIn |BranchNotIn | Other
|Chicago , Newyork , Dehil|
Chicago , Mexico||
|Delhi|
Stockholm||
Delhi
Delhi || ( Location is NorthCity or Location is Downtown)

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2014-01-15 : 16:59:56

Need to remove text (Branch)

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 )'

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-16 : 04:52:24

Is this still an issue?
I though I provided you answer in the other forum

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources