Solved

Convert VB.net Function to sql syntex

Posted on 2010-11-10
4
469 Views
Last Modified: 2012-05-10
Public Function UPCA2UPCE(ByVal UPCA As String) As String
    '
    ' Convert UPC-A to UPC-E format
    '
    ' Written by Glenn J. Schworak (www.schworak.com)
    '
    Dim ValidDigits As String
    Dim Mfg As String
    Dim Prod As String
    Dim x As Integer
   
    If Len(UPCA) <> 12 Or (Left(UPCA, 1) <> "0" And Left(UPCA, 1) <> "1") Or _
       InStr(1, Mid(UPCA, 5, 8), "0000") < 1 Then
        UPCA2UPCE = "INVALID"
    Else
        Mfg = Mid(UPCA, 2, 5)
        Prod = Mid(UPCA, 7, 5)
        If Right(Mfg, 3) = "000" Or Right(Mfg, 3) = "100" Or Right(Mfg, 3) = "200" Then
            ValidDigits = Left(Mfg, 2) & Right(Prod, 3) & Mid(Mfg, 3, 1)
        ElseIf Right(Mfg, 2) = "00" Then
            ValidDigits = Left(Mfg, 3) & Right(Prod, 2) & "3"
        ElseIf Right(Mfg, 1) = "0" Then
            ValidDigits = Left(Mfg, 4) & Right(Prod, 1) & "4"
        Else
            ValidDigits = Left(Mfg, 5) & Right(Prod, 1)
        End If
        UPCA2UPCE = Left(UPCA, 1) & ValidDigits & Right(UPCA, 1)
    End If
End Function

this function converts a string to a different string, I want to do it from sql,

What will be the correct syntex?
0
Comment
Question by:Mr_Ezi
  • 3
4 Comments
 
LVL 15

Expert Comment

by:AmmarR
ID: 34106268
hi

check the code below.

if you send me a sample of the string you send to this function and the desired output, i can get it tweaked to exactly get it right.


Declare @ValidDigits varchar(200)

Declare @Mfg  varchar(200)

Declare @Prod  varchar(200)

Declare @x int



Declare @UPCA varchar(200)

declare @UPCA2UPCE varchar(200) 

 

if LEN(@UPCA) <> 12 or RIGHT(@Mfg,3) = '100' or RIGHT(@Mfg,3) = '200' 

or CHARINDEX('0000',substring(@UPCA, 5, 8)) < 1

	set @UPCA2UPCE = 'INVALID'

else

begin

    SET @Mfg = substring(@UPCA, 2, 5)

    SET @Prod = substring(@UPCA, 7, 5)

     If Right(@Mfg, 3) = '000' Or Right(@Mfg, 3) = '100' Or Right(@Mfg, 3) = '200' 

		set @ValidDigits = Left(@Mfg, 2) + Right(@Prod, 3) + Substring(@Mfg, 3, 1)

     else if Right(@Mfg, 2) = '00'

		set @ValidDigits = Left(@Mfg, 3) + Right(@Prod, 2) + '3'

	 else if Right(@Mfg, 1) = '0'

	    set @ValidDigits = Left(@Mfg, 4) + Right(@Prod, 1) & '4'

	 else

		set  @ValidDigits = Left(@Mfg, 5) + Right(@Prod, 1)   

	 SET @UPCA2UPCE = Left(@UPCA, 1) + @ValidDigits + Right(@UPCA, 1)	

end

Open in new window

0
 
LVL 15

Accepted Solution

by:
AmmarR earned 500 total points
ID: 34106285
this one is correct

i missed and & in the above script

this code below is correct syntax, i need to test it with your string
Declare @ValidDigits varchar(200)

Declare @Mfg  varchar(200)

Declare @Prod  varchar(200)

Declare @x int



Declare @UPCA varchar(200)

declare @UPCA2UPCE varchar(200) 

 

if LEN(@UPCA) <> 12 or RIGHT(@Mfg,3) = '100' or RIGHT(@Mfg,3) = '200' 

or CHARINDEX('0000',substring(@UPCA, 5, 8)) < 1

	set @UPCA2UPCE = 'INVALID'

else

begin

    SET @Mfg = substring(@UPCA, 2, 5)

    SET @Prod = substring(@UPCA, 7, 5)

     If Right(@Mfg, 3) = '000' Or Right(@Mfg, 3) = '100' Or Right(@Mfg, 3) = '200' 

		set @ValidDigits = Left(@Mfg, 2) + Right(@Prod, 3) + Substring(@Mfg, 3, 1)

     else if Right(@Mfg, 2) = '00'

		set @ValidDigits = Left(@Mfg, 3) + Right(@Prod, 2) + '3'

	 else if Right(@Mfg, 1) = '0'

	    set @ValidDigits = Left(@Mfg, 4) + Right(@Prod, 1) + '4'

	 else

		set  @ValidDigits = Left(@Mfg, 5) + Right(@Prod, 1)   

	 SET @UPCA2UPCE = Left(@UPCA, 1) + @ValidDigits + Right(@UPCA, 1)	

end

Open in new window

0
 

Author Closing Comment

by:Mr_Ezi
ID: 34233249
Thanks
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 34236477
anytime
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now