August 20, 2009 at 10:20 am
This moight look simple, i am not getting the result i am looking for.
I want to select database names that end with _CA or _NY and so on, i have 50 such similar codes to filter.
I tried this
SELECT name FROM master.sys.databases WHERE name LIKE '%/___' ESCAPE '/'
but it is also pulling dataabses not in the codes.
August 20, 2009 at 10:25 am
Tara (8/20/2009)
This moight look simple, i am not getting the result i am looking for.I want to select database names that end with _CA or _NY and so on, i have 50 such similar codes to filter.
I tried this
SELECT name FROM master.sys.databases WHERE name LIKE '%/___' ESCAPE '/'
but it is also pulling dataabses not in the codes.
See if this helps:
SELECT name FROM master.sys.databases WHERE right(name,3) in ('_CA', '_NY')
August 20, 2009 at 10:40 am
thanks it worked.
August 20, 2009 at 11:11 am
You can also get it done with a LIKE in your where clause if you read up on the various wildcard patterns. But if you restrict it to an IN list, as suggested above, you are certain to omit dbnames that are not abbreviations, such as dbname_AA, dbname_BB, etc.
declare @table table (dbname varchar(50))
insert into @table
select 'database_CA' union all
select 'database_NY' union all
select 'database_CA_TEST' union all
select 'database_AZ' union all
select 'database_123' union all
select 'database_capital'
select * from @table order by dbname
select * from @table
where dbname like '%[_][a-z][a-z]'
order by dbname
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply