SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert IIF to Case Statements and Instr to charindex or Like


Convert IIF to Case Statements and Instr to charindex or Like

Author
Message
GrassHopper
GrassHopper
Say Hey Kid
Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)

Group: General Forum Members
Points: 685 Visits: 558
I want to convert the ms access sql statement below to a Case statement in sql server TSQL. There are IIF statements that need to be Case statements and InStr statements that need to be converted to Like or charindex? I'm not exactly sure how the syntax for the charindexes should be, but I think I can handle the Case statement. Can anyone help with this?

SELECT Client_Domains.Type
, Client_Domains.Client_VndNbr
, Client_Domains.Client_VndName
, IIf(InStr(1,[Client_VndName]
,' CO')>0 And Mid([Client_VndName]
,InStr(1,[Client_VndName],' CO'),4)=' CO '
,InStr(1,[Client_VndName],' CO')
,IIf(InStr(1,[Client_VndName],' INC')>0,InStr(1,[Client_VndName],' INC'),IIf(InStr(1,[Client_VndName],' LLC')>0,InStr(1,[Client_VndName],' LLC'),0))) AS EndPt, Left([Client_VndName],IIf(InStr(1,[Client_VndName],' CO')>0 And Mid([Client_VndName],InStr(1,[Client_VndName],' CO'),4)=' CO ',InStr(1,[Client_VndName],' CO'),IIf(InStr(1,[Client_VndName],' INC')>0,InStr(1,[Client_VndName],' INC'),IIf(InStr(1,[Client_VndName],' LLC')>0,InStr(1,[Client_VndName],' LLC'),0)))-1) AS ShortName

FROM Client_Domains
WHERE (((Client_Domains.Type)="CltDom") AND ((IIf(InStr(1,[Client_VndName],' CO')>0 And Mid([Client_VndName],InStr(1,[Client_VndName],' CO'),4)=' CO ',InStr(1,[Client_VndName],' CO'),IIf(InStr(1,[Client_VndName],' INC')>0,InStr(1,[Client_VndName],' INC'),IIf(InStr(1,[Client_VndName],' LLC')>0,InStr(1,[Client_VndName],' LLC'),0))))>0) AND ((Client_Domains.CorpVndNbr)=0 Or (Client_Domains.CorpVndNbr) Is Null));



GrassHopper
GrassHopper
Say Hey Kid
Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)

Group: General Forum Members
Points: 685 Visits: 558
Here's an attempt at it. I'm getting an error msg on line 9 (Incorrect syntax near '>'. Can anyone tell me why I am getting the error msg? and If my syntax for the translation is correct?

;With ClientDomains_CTE3
AS
(
SELECT ClientDomains.DomainType
, ClientDomains.ClientVndNbr
, ClientDomains.ClientVndName
, Case ClientVndName
WHEN PATINDEX('% CO%', ClientVndName) > 0
AND SUBSTRING([ClientVndName], PATINDEX('% CO%',[ClientVndName]),4) = ' CO '
THEN PATINDEX('% CO%', ClientVndName)
WHEN PATINDEX('% INC%', ClientVndName) > 0
THEN PATINDEX('% INC%', ClientVndName)
WHEN PATINDEX('% LLC%', ClientVndName) > 0
THEN PATINDEX('% LLC%', ClientVndName)
ELSE 0
END AS EndPt
, LEFT(ClientVndName, CASE ClientVndName
WHEN PATINDEX('% CO%', ClientVndName) > 0
And SUBSTRING(ClientVndName, PATINDEX('% CO%', ClientVndName),4) = ' CO '
THEN PATINDEX('% CO%', ClientVndName)

WHEN PATINDEX('% INC%', ClientVndName) > 0
THEN PATINDEX('% INC%', ClientVndName)

WHEN PATINDEX('% LLC%', ClientVndName) > 0
THEN PATINDEX('% LLC%', ClientVndName)

ELSE 0

END) AS ShortName

FROM ClientDomains
WHERE ClientDomains.DomainType = 'CltDom'
AND (CASE ClientVndName
WHEN PATINDEX('% CO%', ClientVndName) > 0 AND SUBSTRING(ClientVndName, PATINDEX('% CO%', ClientVndName),4) = ' CO '
THEN PATINDEX('% CO%', ClientVndName)
WHEN PATINDEX('% INC%', ClientVndName) > 0
THEN PATINDEX('% INC%', ClientVndName)
WHEN PATINDEX('% LLC%', ClientVndName) > 0
THEN PATINDEX('% LLC%', ClientVndName)
) > 0
AND (ClientDomains.CorpVndNbr = 0 OR ClientDomains.CorpVndNbr IS NULL)
)
Select * From ClientDomains_CTE3


--Here's the orignal ms access vba code I tried translating:
SELECT Client_Domains.Type
, Client_Domains.Client_VndNbr
, Client_Domains.Client_VndName
, IIf(InStr(1,[Client_VndName],' CO')>0 And Mid([Client_VndName],InStr(1,[Client_VndName],' CO'),4)=' CO '
,InStr(1,[Client_VndName],' CO')
,IIf(InStr(1,[Client_VndName],' INC')>0
,InStr(1,[Client_VndName],' INC')
,IIf(InStr(1,[Client_VndName],' LLC')>0
,InStr(1,[Client_VndName],' LLC'),0)
)
) AS EndPt
, Left([Client_VndName]
,IIf(InStr(1,[Client_VndName],' CO')>0 And Mid([Client_VndName],InStr(1,[Client_VndName],' CO'),4)=' CO '
,InStr(1,[Client_VndName],' CO')
,IIf(InStr(1,[Client_VndName],' INC')>0
,InStr(1,[Client_VndName],' INC')
,IIf(InStr(1,[Client_VndName],' LLC')>0
,InStr(1,[Client_VndName],' LLC'),0)
)
)
-1) AS ShortName
FROM Client_Domains
WHERE Client_Domains.Type = "CltDom"
AND
(IIf(InStr(1,[Client_VndName],' CO')>0 And Mid([Client_VndName],InStr(1,[Client_VndName],' CO'),4)=' CO '
,InStr(1,[Client_VndName],' CO')
,IIf(InStr(1,[Client_VndName],' INC')>0
,InStr(1,[Client_VndName],' INC')
,IIf(InStr(1,[Client_VndName],' LLC')>0
,InStr(1,[Client_VndName],' LLC'),0)
)
)
) > 0
AND ((Client_Domains.CorpVndNbr)=0 Or (Client_Domains.CorpVndNbr) Is Null)



higgim
higgim
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2631
Can you provide some sample data and expected output for this please? In the middle of giving it a go now Smile
higgim
higgim
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2631
In the absence of test data here is my spin on it

DECLARE @Client_Domains TABLE
(
Client_VndName NVARCHAR(100) ,
Client_Type NVARCHAR(100) ,
CorpVndNbr INT
)
INSERT INTO @Client_Domains
( Client_VndName, Client_Type, CorpVndNbr )
VALUES ( N'Test 1 CO Lala', 'CltDom', 0 ),
( N'Test 2 CO Lala', 'CltDom', NULL ),
( N'Test 3 CO Lala', 'ABCDom', 1 ),
( N'Test 4 CO Lala', 'ABCDom', NULL ),
( N'Test 5 INC Lala', 'CltDom', NULL ),
( N'Test 6 Lala', 'CltDom', 1 ),
( N'Test 7 Lala', 'CltDom', NULL )

SELECT Client_VndName ,
CASE WHEN CHARINDEX(' CO', Client_VndName) > 0
AND SUBSTRING(Client_VndName,
CHARINDEX(' CO', Client_VndName), 4) = ' CO '
THEN CHARINDEX(' CO', Client_VndName)
WHEN CHARINDEX(' INC', Client_VndName) > 0
THEN CHARINDEX(' INC', Client_VndName)
WHEN CHARINDEX(' LLC', Client_VndName) > 0
THEN CHARINDEX(' LLC', Client_VndName)
ELSE 0
END AS EndPt ,
SUBSTRING(Client_VndName, 1,
CASE WHEN CHARINDEX(' CO', Client_VndName) > 0
AND SUBSTRING(Client_VndName,
CHARINDEX(' CO', Client_VndName), 4) = ' CO '
THEN CHARINDEX(' CO', Client_VndName)
WHEN CHARINDEX(' INC', Client_VndName) > 0
THEN CHARINDEX(' INC', Client_VndName)
WHEN CHARINDEX(' LLC', Client_VndName) > 0
THEN CHARINDEX(' LLC', Client_VndName)
ELSE 0
END) AS ShortName
FROM @Client_Domains
WHERE ( Client_Type = 'CltDom' )
AND ( CorpVndNbr = 0
OR CorpVndNbr IS NULL
)
AND CASE WHEN CHARINDEX(' CO', Client_VndName) > 0
AND SUBSTRING(Client_VndName,
CHARINDEX(' CO', Client_VndName), 4) = ' CO '
THEN 1
WHEN CHARINDEX(' INC', Client_VndName) > 0 THEN 1
WHEN CHARINDEX(' LLC', Client_VndName) > 0 THEN 1
ELSE 0
END = 1




And the final output

Client_VndName EndPt ShortName
Test 1 CO Lala 7 Test 1
Test 2 CO Lala 7 Test 2
Test 5 INC Lala 7 Test 5
higgim
higgim
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1456 Visits: 2631
Actually this way seems to work also

DECLARE @Client_Domains TABLE
(
Client_VndName NVARCHAR(100) ,
Client_Type NVARCHAR(100) ,
CorpVndNbr INT
)
INSERT INTO @Client_Domains
( Client_VndName, Client_Type, CorpVndNbr )
VALUES ( N'Test 1 CO Lala', 'CltDom', 0 ),
( N'Test 2 CO Lala', 'CltDom', NULL ),
( N'Test 3 CO Lala', 'ABCDom', 1 ),
( N'Test 4 CO Lala', 'ABCDom', NULL ),
( N'Test 5 INC Lala', 'CltDom', NULL ),
( N'Test 6 Lala', 'CltDom', 1 ),
( N'Test 7 Lala', 'CltDom', NULL )

SELECT * ,
SUBSTRING(Client_VndName, 1,
CASE WHEN CHARINDEX(' CO', Client_VndName) > 0
AND SUBSTRING(Client_VndName,
CHARINDEX(' CO', Client_VndName), 4) = ' CO '
THEN CHARINDEX(' CO', Client_VndName)
WHEN CHARINDEX(' INC', Client_VndName) > 0
THEN CHARINDEX(' INC', Client_VndName)
WHEN CHARINDEX(' LLC', Client_VndName) > 0
THEN CHARINDEX(' LLC', Client_VndName)
ELSE 0
END) AS ShortName
FROM @Client_Domains
WHERE ( Client_VndName LIKE '% CO %'
OR Client_VndName LIKE '% INC %'
)
AND ( Client_Type = 'CltDom' )
AND ( CorpVndNbr = 0
OR CorpVndNbr IS NULL
)


GrassHopper
GrassHopper
Say Hey Kid
Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)

Group: General Forum Members
Points: 685 Visits: 558
Thanks for the reply with good code. Sorry I took long getting back but I went back to the business person and asked for these requirements in the original query and I think I simplified it much more. I am getting an erro msg on "as ShortName on Line 13. But I think this will give me what i want.


Data:
clientvndname(fieldname)
SPECIALITES PRODAL 1975
ABUNDANCE MARKETING INC
ACH FOOD COMPANIES C/O 910040
ACTION LIGHTING
AON REED STENHOUSE INC
LEAHY ORCHARDS INC.
AQPP
CANADIAN SPRINGS
ARLA FOODS INC
HILLMANS TRANSFER CO LTD

Results desired:
SPECIALITES PRODAL 1975
ABUNDANCE MARKETING
ACH FOOD COMPANIES C/O 910040
ACTION LIGHTING
AON REED STENHOUSE
LEAHY ORCHARDS
AQPP
CANADIAN SPRINGS
ARLA FOODS
HILLMANS TRANSFER


;With ClientDomains_CTE3
AS
(
SELECT ClientDomains.DomainType
, ClientDomains.ClientVndNbr
, ClientDomains.ClientVndName
, LEFT(ClientVndName
, RTRIM(REPLACE(
REPLACE(
REPLACE(
REPLACE(ClientVndName, ' CO ', ' ')
,' INC ', ' ')
,' INC.', ' ')
,' LTD ', ' ')) as ShortName
FROM ClientDomains
WHERE ClientDomains.DomainType = 'CltDom'
AND PATINDEX('% INC %',DomainName) > 0 OR PATINDEX('% CO %',DomainName) > 0 OR PATINDEX('% LTD %',DomainName) > 0 OR PATINDEX('% CO.',DomainName) > 0
AND (ClientDomains.CorpVndNbr = 0 OR ClientDomains.CorpVndNbr IS NULL)
)
Select * From ClientDomains_CTE3



GrassHopper
GrassHopper
Say Hey Kid
Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)Say Hey Kid (685 reputation)

Group: General Forum Members
Points: 685 Visits: 558
found my error, this works. Thanks! higgim, your code gave me the structure I needed for my query below.


;With ClientDomains_CTE3
AS
(
SELECT ClientDomains.DomainType
, ClientDomains.ClientVndNbr
, ClientDomains.ClientVndName
, RTRIM(REPLACE(
REPLACE(
REPLACE(
REPLACE(ClientVndName, ' CO ', ' ')
,' INC ', ' ')
,' INC.', ' ')
,' LTD ', ' ')
) as ShortName
FROM ClientDomains
WHERE ClientDomains.DomainType = 'CltDom'
AND (ClientVndName LIKE '% INC %' OR ClientVndName LIKE '% CO %' OR ClientVndName LIKE '% INC.%' OR ClientVndName LIKE '% LTD %')
AND (ClientDomains.CorpVndNbr = 0 OR ClientDomains.CorpVndNbr IS NULL)
)
Select * From ClientDomains_CTE3



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search