<div>
I don't know how MS SQL uses them, but the pattern you want is:<br />
<br />

<pre><code class="code-1 nolinks" id="code-259471"><span>^[A-Z]{3}[a-z]{3}$</span>
</code></pre>
</div>


<div>
should be something like this<br />
<br />
Select ((upper(substring(MyCharac<wbr />ter, 12, 3)) + (lower(substring(MyCharact<wbr />er, 15,3))) as UpperLower<br />
From MyTable
</div>


<div>
Play around with the first part of the syntax<br />
<br />
Select upper(substring(MyCharacte<wbr />r, 12, 3))<br />
From MyTable<br />
<br />
and understand the syntax first.
</div>


<div>
So if I want just the first 3 character should be something like this<br />
Select upper(substring(MyCharacte<wbr />r, 1, 3)) &nbsp; &nbsp;--- select the first three character<br />
&nbsp; + lower(substring(MyCharacte<wbr />r,4,3)) &nbsp;as UpperLower<br />
<br />
from Mytable <br />
<br />

</div>


<div>
60MXG: they want only ROWS that are 3 UPPER and 3 lower. &nbsp;Not rows to be converted to 3 UPPER and 3 lower.<br />
<br />
You can use SQL CLR to do regex validation (<a href="http://msdn.microsoft.com/en-us/magazine/cc163473.aspx" rel="ugc">http://msdn.microsoft.com/en-us/magazine/cc163473.aspx</a>), or this UDF will do your EXACT use case.<br />
<br />
select dbo.fn_is_UPPlow(N'AfCabc'<wbr />)<br />
select dbo.fn_is_UPPlow(N'ABCabc'<wbr />)<br />

<pre><code class="code-10 nolinks" id="code-259500"><span>drop function dbo.fn_is_UPPlow</span>
<span>go</span>
<span>create function dbo.fn_is_UPPlow(@string nvarchar(max))</span>
<span>returns bit</span>
<span>as</span>
<span>begin</span>
<span>declare @pass bit</span>
<span> </span>
<span>if datalength(@string)/2=6</span>
<span>begin</span>
<span>     if  ascii(substring(@string,1,1))between 65 and 90</span>
<span>     and ascii(substring(@string,2,1))between 65 and 90</span>
<span>     and ascii(substring(@string,3,1))between 65 and 90</span>
<span>     and ascii(substring(@string,4,1))between 97 and 122</span>
<span>     and ascii(substring(@string,5,1))between 97 and 122</span>
<span>     and ascii(substring(@string,6,1))between 97 and 122</span>
<span>          set @pass=1</span>
<span>     else</span>
<span>          set @pass=0</span>
<span>     </span>
<span>	</span>
<span>end</span>
<span>else</span>
<span>     set @pass=0</span>
<span> </span>
<span>return @pass</span>
<span>end</span>
<span> </span>
<span>GO</span>
</code></pre>
</div>


<div>
I currently have stomething like this:<br />
<br />
SELECT oc.my_id, oc.CouponCode, o.OrderID, o.OrderDate, o.TotalCharges, c.Name, c.DiscountAmount<br />
FROM dbo.ac_OrderCoupons AS oc,<br />
dbo.ac_Orders AS o,<br />
dbo.ac_Coupons AS c<br />
WHERE oc.OrderId = o.OrderId AND &nbsp;c.CouponCode = oc.CouponCode<br />
<br />
and i need to make sure I only display the oc.CouponCode (the coupon codes) that have that filter criteria. Where do I put the regular expression? Thank you!
</div>


<div>
SELECT oc.my_id, <br />
upper(substring(oc.CouponC<wbr />ode,1,3)) + lower(substring(oc.CouponC<wbr />ode,4,3)) as CouponCode &nbsp; &nbsp;<br />
o.OrderID, <br />
o.OrderDate, <br />
o.TotalCharges, <br />
c.Name, <br />
c.DiscountAmount<br />
FROM dbo.ac_OrderCoupons AS oc,<br />
dbo.ac_Orders AS o,<br />
dbo.ac_Coupons AS c<br />
WHERE oc.OrderId = o.OrderId AND &nbsp;c.CouponCode = oc.CouponCode<br />

</div>


<div>
woops forget the commas<br />
<br />
SELECT oc.my_id, <br />
upper(substring(oc.CouponC<wbr />ode,1,3)) + lower(substring(oc.CouponC<wbr />ode,4,3)) as CouponCode ,<br />
o.OrderID, <br />
o.OrderDate, <br />
o.TotalCharges, <br />
c.Name, <br />
c.DiscountAmount<br />
FROM dbo.ac_OrderCoupons AS oc,<br />
dbo.ac_Orders AS o,<br />
dbo.ac_Coupons AS c<br />
WHERE oc.OrderId = o.OrderId AND &nbsp;c.CouponCode = oc.CouponCode
</div>


<div>
Using my function, and our SQL (fixed to use joins):<br />
<br />
<br />
SELECT oc.my_id, oc.CouponCode, o.OrderID, o.OrderDate, o.TotalCharges, c.Name, c.DiscountAmount<br />
FROM dbo.ac_OrderCoupons AS oc<br />
&nbsp; join dbo.ac_Orders AS o<br />
&nbsp; &nbsp; on oc.OrderId = o.OrderId<br />
&nbsp; join dbo.ac_Coupons AS c<br />
&nbsp; &nbsp; on c.CouponCode = oc.CouponCode<br />
&nbsp; &nbsp; and dbo.fn_is_UPPlow(oc.coupon<wbr />code) = 1
</div>


<div>
How about this?<br />
<br />

<pre><code class="code-1 nolinks" id="code-259507"><span>SELECT whatever FROM whereever WHERE dbo.RegExMatch (oc.CouponCode,'^[A-Z]{3}[a-z]{3}$')=1</span>
</code></pre>
</div>


<div>
ic what you are saying! &nbsp;Misunderstand the question!
</div>


<div>
BrandonGalderisi running your code I'm getting this error:<br />
<br />
<br />
Msg 4121, Level 16, State 1, Line 1<br />
Cannot find either column &quot;dbo&quot; or the user-defined function or aggregate &quot;dbo.fn_is_UPPlow&quot;, or the name is ambiguous.
</div>


<div>
You didn't create the function he gave you!
</div>


<div>
The function is in post <a href="http://#a23727562" rel="ugc">http://#a23727562</a>
</div>


<div>
Sorry... <a href="http:#a23727562" rel="ugc">http:#a23727562</a>
</div>


<div>
Here's the whole thing with Regex.<br />
<br />

<pre><code class="code-1 nolinks" id="code-259512"><span>SELECT oc.my_id, oc.CouponCode, o.OrderID, o.OrderDate, o.TotalCharges, c.Name, c.DiscountAmount</span>
<span>FROM dbo.ac_OrderCoupons AS oc,</span>
<span>dbo.ac_Orders AS o,</span>
<span>dbo.ac_Coupons AS c</span>
<span>WHERE oc.OrderId = o.OrderId AND  c.CouponCode = oc.CouponCode</span>
<span>  AND dbo.RegExMatch (oc.CouponCode,'^[A-Z]{3}[a-z]{3}$')=1</span>
</code></pre>
</div>


<div>
This did exactly what I needed. Thank you much!
</div>


<div>
Just for fun, can you verify whether or not my Regex solution worked? &nbsp;I'm curious.<br />

</div>


<div>
moorereason I verified yours but I get this error:<br />
<br />
<br />
Msg 4121, Level 16, State 1, Line 1<br />
Cannot find either column &quot;dbo&quot; or the user-defined function or aggregate &quot;dbo.RegExMatch&quot;, or the name is ambiguous.
</div>


<div>
moorereason we got sniped out by Sharath 123! &nbsp;All our work for nothing! &nbsp;Ke ke! &nbsp;:)
</div>


<div>
Though Sharath 123 got the correct response I truly appreciate everybody trying to help me. <br />
Thank you very much!
</div>


<div>
el930692: mine will do what you have asked. &nbsp;You just didn't bother to create the function that I posted.<br />
<br />
60MXG: you didn't get sniped, you had it wrong from the beginning. &nbsp;You kept posting ways to SELECT it as 3 uppers and 3 lowers and you weren't verifying that it &quot;WAS&quot; 3 uppers and 3 lowers as they requested.<br />
<br />
I'm upset because I posted a working solution FIRST and didn't get even an assist.
</div>


<div>
I feel your pain BrandonGalde! &nbsp;Sharath used my substring function and get that to work. &nbsp;I still think Sharath method is much simpler. &nbsp;No hard feeling! &nbsp;Move on to next topic!
</div>


<div>
60MXG - The trick is you need to place your SUBSTRING in the WHERE clause. At the same time, you need to take care of case-sensitive. <br />
I didn't check the function created by Brandon but i guess it will work. <br />
The asker may be looking for simpler solution rather than creating a function. <br />
<br />
We are all here to help each other and enhance our skills. <br />
Many times, i didn't get any points even though my solution is one of the solutions to resolve the issue. <br />
I raised this concern long back in some other threrad. Its all depend on the importnace, urgency and ofcourse the asker's mood. <br />
<br />

</div>


<div>
<div class="content wysiwyg-content">
Hello,<br />
I've very new to SQL regular expressions and I need a MS SQL regular expression that will return only a strings &nbsp;six characters long and it has first three letters upper case and last three letters lower case like : ABCabc<br />
I truly appreciate your help.
</div>
</div>


Hello,
I've very new to SQL regular expressions and I need a MS SQL regular expression that will return only a strings  six characters long and it has first three letters upper case and last three letters lower case like : ABCabc
I truly appreciate your help.

SQL regular expressions help

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 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.