Substring 1CT.....99CT Excluded 1

[codrutza]

The result of a selection is:
……..
T3012246/1
T3011808/14
T3011808/15
T3012316/3
T3011840/1
T3012316/2CT
T3011982/1
T3011982/2
T3012326/1CT
T3011854/12CT
T3012027/1
T3012027/2
T3012081/1
T3012027/3
T3011808/6
T3011808/7
T3011778/1
T3012178/1CT
T3012178/2
T3011935/1CT
T3012191/1
T3012128/1CT
T3012074/1
T3011730/3CT
T3012326/2
T3012251/7CT
…..

I have to obtain all the records without /1CT, /2CT…./99CT
…
T3012246/1
T3011808/14
T3011808/15
T3012316/3
T3011840/1
T3011982/1
T3011982/2
T3012027/1
T3012027/2
T3012081/1
T3012027/3
T3011808/6
T3011808/7
T3011778/1
T3012178/2
T3012191/1
T3012074/1
T3012326/2
…


And I don’t know how to write the selection

Can you help?

 

[gmmastros]

This should work.

Select  *
From	YourTable
Where	YourColumn Not Like '%/[0-9]CT%'
	And YourColumn Not Like '%/[0-9][0-9]CT%'

The important part is %/[0-9]CT%

The first percent allows any number of characters at the beginning.
The last percent allows any number of characters after the search.
[0-9] searches for any number.
CT are literals.

This search will return matches for /(number)CT. The NOT part in front of the like reverses the condition. To accommodate the situation where there are two numbers, another condition is added.

Based on your sample data, looks like you would get the same results by just returning rows that do not end in CT.

Select  *
From	YourTable
Where	YourColumn Not Like '%CT'

-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