Ignoring blank or particular characters in a string

[TorrediPisa]

Hello to everyone!

Suppose you have a simple one column Table (TABLE_01) like this:
Mark
AA_003
AA 003
AA003
AB_055
.....

I would like to create a query which returns:
AA_003
AA 003
AA003

when I search 'AA003'
For Example:
SELECT some_Function_here(MARK) FROM TABLE_01 WHERE MARK = 'AA003'

Is there exist such a function?


Thank you for yr attention
Best Regards
TdP

 

[SQLDenis]

One way
create table mark (value varchar(50))
insert into mark
select 'AA_003' union all
select 'AA 003' union all
select 'AA003' union all
select 'AB_055'


select * from mark where replace(replace(value,' ',''),'_','') = 'AA003'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

For this particular case:

select * 
from blah
where (Mark like 'AA[ \_]003' escape '\' or Mark = 'AA003')

If " " or "_" can appear anywhere in Mark, you'll have to use something like:

select * 
from blah
where replace(replace(Mark, ' ', ''), '_', '') = 'AA003'

Needless to say - this sucks .

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)