SQLTeam.com | Weblogs | Forums

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


#1

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')


#2

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?


#3

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


#4

try this in Excel ?

=SUBSTITUTE(A1,"-","")

#5

Thanks!
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.


#6

what data type is IdenNumber

use sqlteam
go

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

#7

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


#8

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?


#9

Thanks Scott. It worked that way.


#10

yes its just a sample table to test out your scenario