SQLTeam.com | Weblogs | Forums

Need to ignore dashes when using the where statement in SQL - example provided


How can I have this SQL query to ignore the dashes like the three numbers below please?

select * from [DATABASE] where IdenNumber in ('800585-80-285','800585-80-103','800585-80-368')


Are you doing this manually or through a stored procedure?
Also is that '800585-80-285','800585-80-103','800585-80-368' coming in as a parameter?


Manually, and that is the way it is provided to me. I want to avoid spending time removing those dashes in excel or manually.


try this in Excel ?



I did, and it works there, but I know there is a way in SQL to avoid doing it in excel. I think REPLACE could be used but not sure how.


what data type is IdenNumber

use sqlteam

create table _database(IdenNumber varchar(150) )
insert into _database
select '80058580285' union
select '80058580103' union
select '80058580368' union
select '98989'

select * 
  from _database a
  left join  [dbo].[DelimitedSplit8K]('800585-80-285,800585-80-103,800585-80-368',',') b
  on a.IdenNumber = replace(b.Item,'-','')

drop table _database


where IdenNumber in (REPLACE('800585-80-285', '-', ''),REPLACE('800585-80-103', '-', ''),REPLACE('800585-80-368', '-', ''))


Thanks, I will give it a try, and I am guessing (forgive me I am new to SQL) the last sentence of your query will delete the table I just created?


Thanks Scott. It worked that way.


yes its just a sample table to test out your scenario