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?
 
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
 
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
cyberkiwiCommented:
Any number of digits really means the very last one is a digit.
0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.