Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Convert VB.net Function to sql syntex

Posted on 2010-11-10
4
Medium Priority
?
496 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
[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
  • Learn & ask questions
  • 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 2000 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 Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

636 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