[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 724

Convert Excel function to SQL

Need help converting this Excel nested If function to SQL script

=RIGHT(B1,LEN(B1)-IF(OR(MID(B1,1,1)="0",MID(B1,1,1)="9"),
IF(OR(MID(B1,2,1)="0",MID(B1,2,1)="1"),
IF(OR(MID(B1,3,1)="0",AND(MID(B1,3,1)="1",MID(B1,1,3)<>"001"),MID(B1,1,3)="900"),
IF(OR(MID(B1,4,1)="0",MID(B1,1,4)="9011",AND(MID(B1,1,5)="00021",LEFT(R95,2)="BR")),
IF(OR(MID(B1,5,1)="0",AND(MID(B1,1,5)="00021",LEFT(R95,2)="BR")),
IF(MID(B1,6,1)="0",6,5),4),3),2),1),0))

Ex: 00039019746598 should result in 39019746598

0
vdr1620
2 Solutions

Commented:
0

Commented:
vdr - your basic structure will be as follows.  From your previous question, I think you have a sense of how to write the SQL, you just needed the structure.  Your Excel expression has 6 conditions to test and that's reflected in the structure below.  Have a look and let me know us know if you need more detailed assistance.

IF ( Boolean_expression1 )
BEGIN
Sql Statement Block
END
ELSE
BEGIN
IF ( Boolean_expression2 )
BEGIN
Sql Statement Block
END
ELSE
BEGIN
IF ( Boolean_expression3 )
BEGIN
Sql Statement Block
END
ELSE
BEGIN
IF ( Boolean_expression4 )
BEGIN
Sql Statement Block
END
ELSE
BEGIN
IF ( Boolean_expression5 )
BEGIN
Sql Statement Block
END
ELSE
BEGIN
IF ( Boolean_expression6 )
BEGIN
Sql Statement Block
END
ELSE
BEGIN
Sql Statement Block
END
END
END
END
END
END
0

Commented:
Previous Question: If Expression in Excel
0

Commented:
To put this into context of your previous question:

Boolean Expression1 (BE1) is the equivalent of A: in the decision tree
Boolean Expression2 (BE2) = B:
BE3 = C:
BE4 = D:
BE5 = E:
BE6 = F:

Also, you'll need to use SUBSTRING() instead of MID():  MSDN SUBSTRING()
0

Commented:
Rather than sifting through a bunch of excel functions, can you just state the criteria in English?
0

Commented:
Somethinhg like this perhaps:
``````SELECT 	RIGHT(B1, LEN(B1) -
CASE
WHEN SUBSTRING(B1, 1, 1) IN ('0', '9') THEN
CASE
WHEN SUBSTRING(B1, 2, 1) IN ('0', '1') THEN
CASE
WHEN SUBSTRING(B1, 3, 1) = '0' OR (SUBSTRING(B1, 3, 1) = '1' AND SUBSTRING(B1, 1, 3) <> '001') OR  SUBSTRING(B1, 1, 3) = '900' THEN
CASE
WHEN SUBSTRING(B1, 4, 1)= '0' OR SUBSTRING(B1, 1, 4) = '9011' OR (SUBSTRING(B1, 1, 5) = '00021' AND LEFT(R95, 2) = 'BR') THEN
CASE
WHEN SUBSTRING(B1, 5, 1)='0' OR (SUBSTRING(B1, 1, 5) = '00021' AND LEFT(R95,2) = 'BR') THEN
CASE
WHEN SUBSTRING(B1,6,1) = '0' THEN 6
ELSE 5
END
ELSE 4
END
ELSE 3
END
ELSE 2
END
ELSE 1
END
ELSE 0
END
)
``````
0

Author Commented:
Thanks again Mark for trying to put this together.. but i wanted to do this using CASE rather than IF as you will not be able to implement IF in an Inline statement

Thanks acperkins... this is exactly what i was looking for.. I will test it and let you know
0

Commented:
No worries, vdr.  Hope ac's solution works for you.
0

Commented:
From the prior related question:
http:Q_27832816.html

Negating the conditions greatly simplifies the resulting CASE statement.  I used the ac_perkins T-SQL as my template, since he had already changed the Excel MID() functions into SUBSTRING() functions and uses the simpler IN() clause.
``````SELECT 	RIGHT(B1, LEN(B1) -
CASE
WHEN NOT(SUBSTRING(B1, 1, 1) IN ('0', '9')) THEN 0
WHEN NOT(SUBSTRING(B1, 2, 1) IN ('0', '1')) THEN 1
WHEN NOT(SUBSTRING(B1, 3, 1) = '0' OR (SUBSTRING(B1, 3, 1) = '1' AND SUBSTRING(B1, 1, 3) <> '001') OR  SUBSTRING(B1, 1, 3) = '900' )) THEN 2
WHEN NOT(SUBSTRING(B1, 4, 1)= '0' OR SUBSTRING(B1, 1, 4) = '9011' OR (SUBSTRING(B1, 1, 5) = '00021' AND LEFT(R95, 2) = 'BR')) THEN 3
WHEN NOT(SUBSTRING(B1, 5, 1)='0' OR (SUBSTRING(B1, 1, 5) = '00021' AND LEFT(R95,2) = 'BR')) THEN 4
WHEN SUBSTR(B1,6,1)= '0' THEN 6
ELSE 5
END
)
``````

Note: I don't know where the R95 reference points, but assume it is valid.
0

Author Commented:
aikimark, I did try negating the conditions but ran into a snag and thought it would not work.. but you proved me wrong... Thanks
0

Author Commented:
Thank you guys..
0

Featured Post

Tackle projects and never again get stuck behind a technical roadblock.