Solved

# Convert VB.net Function to sql syntex

Posted on 2010-11-10
476 Views
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
Question by:Mr_Ezi
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3

LVL 15

Expert Comment

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
``````
0

LVL 15

Accepted Solution

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
``````
0

Author Closing Comment

ID: 34233249
Thanks
0

LVL 15

Expert Comment

ID: 34236477
anytime
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Progress
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
###### Suggested Courses
Course of the Month7 days, 3 hours left to enroll