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')
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 ?
=SUBSTITUTE(A1,"-","")
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.
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
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