SQLServer 2008R2 standard pattern matching question

[wbodger]

I have data that (in part) looks like this

T-3B7020-1                    	3B7020-16EFC                  
T-3B7020-1                    	3B7020-16EFC                  
T-3B7020-1                    	3B7020-16EFY                  
T-3B7020-1                    	3B7020-16EFY                  
T-3B7020-1                    	3B7020-16EVE                  
T-3B7020-1                    	3B7020-16EVE                  
T-3B7020-1                    	3B7020-1APX                   
T-3B7020-1                    	3B7020-1APX                   
T-3B7020-1                    	3B7020-1ASN                   
T-3B7020-1                    	3B7020-1ASN                   
T-3B7020-1                    	3B7020-1CTG                   
T-3B7020-1                    	3B7020-1CTG                   
T-3B7020-1                    	3B7020-1CZP                   
T-3B7020-1                    	3B7020-1CZP

The values on the left are templates for StockCodes, the values on the right are StockCodes. I have maybe 80 Templates and thousands of StockCodes. The templates have been updated and I now need to apply those updates to the StockCodes based off of them. However, in the above list 3B7020-16EVE and 3B7020-1APX should not both match up with T-3B7020-1. Instead, 3B7020-1APX should match up with T-3B7020-1 and 3B7020-16EVE should match up with T-3B7020-16. If there was some way I could designate that every character after len(Template)-2 in the StockCode has to be a character I believe I could make this work (*so, left(StockCode,len(template)-2)+(AnyAlpha)), but I can not come up with how to make that work. Any suggestions?

Thanks,
Willie

 

[wbodger]

And of course right after I post this I get most of the way to my answer. I can use

where BomOperations_TEST.StockCode like cte.TStockCode+'[A-Z][A-Z][A-Z]'

the only change I need is that I need to have a variable number of alpha only characters.

Thanks,
wb

 

[gmmastros]

I don't know if you solved this yet, but there is a little string manipulation you can do that may help. I say "MAY" because I'm not sure this is what you really want.

Anyway... it seems to me like you want to remove the last bit of alpha characters from your string. Here's a method for doing that.

Declare @Temp VarChar(20)

Set @Temp = '3B7020-1CZP'

Select Left(@Temp, Len(@Temp) - PatIndex('%[a-z][0-9]%', Reverse(@Temp)))

Basically, this will reverse the string (which really just reverses the order of the characters). It then looks for a transition from letter to number (because we really want the last transition from number to character in the un-reversed version of the data). Then, it is a simple matter to use the left function to get the data you are looking for.

Again... I don't know if this will help, but I hope it does.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

I wish that was what I was looking for, but rather I need to have the [a-z] (the alpha only characters) at the end of the stockcode to be of a variable length. The Template is (for instance) T-3B7020-1 and this would match 3B7020-1CZP or 3B7020-1CZPKAS or 3B7020-1ADPW etc.

Thanks,
Willie

 

[gmmastros]

The code I posted should accommodate variable length alpha characters at the end.

Take a quick look at this:

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('3B7020-1CZP')
Insert Into @Temp Values('3B7020-1CZPKAS')
Insert Into @Temp Values('3B7020-1ADPW')

Select Left(Data, Len(Data) - PatIndex('%[a-z][0-9]%', Reverse(Data)))
From @Temp

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

I couldn't quite get your code to do what I was looking to do, but I did get it to work like this (or perhaps this is what you were saying)

;with cte as
(
select StockCode, substring(StockCode+'%',3,LEN(StockCode)-2) as TStockCode, LEN(StockCode)-2 as LengthTStockCode
from BomOperations_TEST
where StockCode like 'T%'
and WorkCentre = 'GC'
)

select cte.StockCode, BomOperations_TEST.StockCode, WorkCentre, IRunTime, ToolSet
from BomOperations_TEST, cte
where BomOperations_TEST.StockCode like cte.TStockCode+'[A-Z]%'
and WorkCentre in ('GC','10')

I realized that if that first character was alpha then I would be fine to just use a general wildcard after that.

Thanks,
Willie