Solved

Convert Excel function to SQL

Posted on 2012-08-17
711 Views
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
Question by:vdr1620

LVL 92

Expert Comment

0

LVL 10

Expert Comment

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

LVL 10

Expert Comment

Previous Question: If Expression in Excel
0

LVL 10

Expert Comment

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

LVL 31

Expert Comment

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

LVL 75

Assisted Solution

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

LVL 16

Author Comment

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

LVL 10

Expert Comment

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

LVL 44

Accepted Solution

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

LVL 16

Author Comment

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

LVL 16

Author Closing Comment

Thank you guys..
0

Featured Post

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!