Solved

Convert VB.net Function to sql syntex

Posted on 2010-11-10
4
474 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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 …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

749 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