<div>
<div class="content wysiwyg-content">
I need help creating SQL Select command to perform a look up against another table with the ability to transform the values. &nbsp;Not sure if this is even possible with a SQL Server but thought I would give it try anyway. &nbsp;So here is what I need. &nbsp; 
 
I need a SQL Select command to match data from table1 to table2 based on this rule: 
&nbsp; - &nbsp;remove spaces from number 
&nbsp; - &nbsp;If length of Table1.Number = 14, 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;search Table2.GTIN Number Column 
&nbsp; - &nbsp;ElseIF length is 10, 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Search on UPC Column 
&nbsp; 
Table1 &nbsp; &nbsp;(1 column) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Table2 &nbsp;( 2 columns) 
----------------------------- &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -------------------------------------------------- 
Number &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; UPC Number &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GTIN Number 
000 38000 10061 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0000000000 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;00038000100611 
38000 00010 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3800000010 
 
Expected Results: 
COL1 
00038000100611 
3800000010
</div>
</div>

I need help creating SQL Select command to perform a look up against another table with the ability to transform the values.  Not sure if this is even possible with a SQL Server but thought I would give it try anyway.  So here is what I need.  

I need a SQL Select command to match data from table1 to table2 based on this rule:
  -  remove spaces from number
  -  If length of Table1.Number = 14,
         search Table2.GTIN Number Column
  -  ElseIF length is 10,
         Search on UPC Column
 
Table1    (1 column)                      Table2  ( 2 columns)
-----------------------------                     --------------------------------------------------
Number                                           UPC Number                        GTIN Number
000 38000 10061 1                         0000000000                        00038000100611
38000 00010                                    3800000010

Expected Results:
COL1
00038000100611
3800000010

Help with MS SQL Server Select Command

A regular expression ("regex") is a sequence of characters that define a search pattern, mainly for use in pattern matching with strings, or string matching, i.e. "find and replace"-like operations. Regular expression processors are found in several search engines, search and replace dialogs of several word processors and text editors, and in the command lines of text processing utilities, such as sed and AWK. Many programming languages provide regular expression capabilities, some built-in, for example Perl, JavaScript, Ruby, AWK, and Tcl, and others via a standard library, for example .NET languages, Java, Python and C++ (since C++11). Most other languages offer regular expressions via a library.

Regular Expressions

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.