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

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

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

Appreciate your Help!!!!!!!
0
vdr1620
Asked:
vdr1620
2 Solutions
 
Patrick MatthewsCommented:
Please state your requirements in a few sentences :)
0
 
mark_harris231Commented:
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
 
mark_harris231Commented:
Previous Question: If Expression in Excel
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
mark_harris231Commented:
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
 
awking00Commented:
Rather than sifting through a bunch of excel functions, can you just state the criteria in English?
0
 
Anthony PerkinsCommented:
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
	)

Open in new window

0
 
vdr1620Author 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
 
mark_harris231Commented:
No worries, vdr.  Hope ac's solution works for you.
0
 
aikimarkCommented:
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
	)

Open in new window


Note: I don't know where the R95 reference points, but assume it is valid.
0
 
vdr1620Author 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
 
vdr1620Author Commented:
Thank you guys..
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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