SQL LIKE command

Hi there,

I need your help to get records from a table where a particular field end with any numbers of any digits. For make it clear lets say i have records with values 'AirShow', 'AirShow - Copy 1', 'Air Show - Copy 2', 'Airshow - Copy1 - Copy1'.

Now I need my script to return me records 'AirShow - Copy 1' and AirShow - Copy 2',  but not 'AirSHow' and 'AirShow - Copy1 - Cop1'. How can I do that? I can't use like since it will return other records and I don't want to use CLR functions.
renjitkumarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
select * from tbl
where field1 like '%[0-9]'
0
EvilPostItCommented:
Here are a couple of examples.

WHERE Column LIKE 'AirShow %' 

Open in new window

WHERE Column LIKE 'AirShow%' AND NOT Column='AirShow'

Open in new window

0
cyberkiwiCommented:
Any number of digits really means the very last one is a digit.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

CluskittCommented:
WHERE field LIKE 'Airshow Copy[0-9]'
0
Pratima PharandeCommented:
select * from tbl
where field1 like '% [0-9]'
0
CleohwinyaCommented:
select * from tbl
where field1 like '% [0-9]'
0
EvilPostItCommented:
Just to slightly ammend CyberWiki's one.

select * from tbl
where field1 like 'AirShow - Copy [0-9]' 

Open in new window

0
Alpesh PatelAssistant ConsultantCommented:
WHERE Column LIKE 'AirShow %-%Copy [1-9]'
0
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
Hi,

you can use following if data is only of specified expression

WHERE fieldName Like 'Airshow Copy[0-9]'

and if you want it make general to restrict any data with more than one - then you can also use
WHERE fieldName Like '%[0-9]'  and not like fieldName Like '%-%-%'

Thanks
0
renjitkumarAuthor Commented:
I mean any number of digits can appear at the end. Lets say AirShow - Copy 1111 also will be there in the list.
0
renjitkumarAuthor Commented:
Oh.... by the way the idea of this is. When I copy the exact records already exists in table i need ID and Name to be changed. ID is doesn't matter since it is an identity column. But Name should be like The original record's name + Copy + the count of copy. The count of copy means lets say i took 2 copies of same record then the newly created records will behaving names 'AirShow - Copy 1' and 'AirShow - Copy 2'. If I create one more then the name will be 'AirShow - Copy 3'. And it is endless :)
0
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
WHERE
(fieldName Like '%[0-9]'  Or fieldName Like '%[0-9][0-9]'  Or fieldName Like '%[0-9][0-9][0-9]'  Or fieldName Like '%[0-9][0-9][0-9][0-9]' )
and not like fieldName Like '%-%-%'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hyphenpipeCommented:
Why do you not want Airshow - Copy1 - Copy1 returned?  It matches your criteria of ending with a digit.

I am just trying to get a better sense of what you are trying to accomplish.
0
renjitkumarAuthor Commented:
Hello hyphenpipe,

The reason why I don't want 'Airshow - Copy1 - Copy1' is not returned because it is copied from 'Airshow - Copy'. Then off course the new record name should be  'Airshow - Copy1 - Copy1'. But when I copy from 'Air Show', then it should get the count of records which is already copied exactly from that record and amend it with new record.

It is the similar functionality when we copy a notepad into same location in windows. It will be named as 'Copy of .....' If we copy the copied file then the name of new file will be 'Copy of Copy of.....'. I have an extra requirement which need to show the number of copy. Hope you understand. Let me know any clarification required.
0
renjitkumarAuthor Commented:
You got it right. Initial testing gave me positive result. Let me try to break it now. :)

Thanks alot.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.