SQL SELECT... WHERE Replace(...) = 'value'

[Dadda]

I have a column in a table containing a sort of social security number. The mask for these numbers are nnnnnn-nnnn (750701-1234). I want to use the input of only numbers to be able to find the right post, for example if I search for "7507011234" I want the SQLquery to actually find the post containing "750701-1234".

So, I've tried the following:

SELECT ID, Pnr, KortNr 
FROM tbl_Person
WHERE (Replace(Pnr, '-', '')) = '7507011234'

...and a couple of variants but I'm not getting this to work. Is this the way to do it or is there another way, perhaps to SELECT only numbers from the 'Pnr'-column?

Any ideas someone?

 

[dhookom]

[Qutoe Dadda]but I'm not getting this to work[/quote]We are having a little problem seeing your exact results. Can you share what isn't working?

What have you tried to do for troubleshooting? What do you see if you use:

SELECT ID, Pnr, KortNr, Replace(Pnr, '-', '') as PnrNoHyphen
FROM tbl_Person

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

What is the actual input mask? Most of the time the formatting is not actually saved unless you specify in the second parameter of the input mask. So you may not have to do anything. 750701-1234 may be saved as 7507011234 but displayed as 750701-1234.

 

[Dadda]

Thanks guys for your answers...

dhookom: That one I've tried, I get the result I want, a list with the correct formatting in the column "PnrNoHyphen". The problem seems to lie with the WHERE-statement. If I use the code

SELECT ID, Pnr, KortNr 
FROM tbl_Person
WHERE (Replace(Pnr, '-', '')) = '7507011234'

I get a 'type mismatch' error. I tried to take out the "single quotation mark" around '7507011234' but I still get the 'type mismatch' error. Is the problem that I mix different datatypes? If so, is there anyway to get around it in the SQL statement?

MajP: The data comes from a ASP-form where the users are forced to type this number with this "input mask" (nnnnnn-nnnn). It has to be in this way because all the inputs contain checknumbers and validation. So the actual data is '750701-1234'.

Thanks again for taking your time...

 

[randy700]

WHERE Left(Pnr,6) = Left(InputField,6) AND
Right(Pnr,4) = Right(InputField,4)

Randy

 

[dhookom]

Is Pnr a number field? I can't imagine why it would be however if it is, try:

SELECT ID, Pnr, KortNr 
FROM tbl_Person
WHERE Pnr = 7507011234

Duane
Hook'D on Access
MS Access MVP

 

[Dadda]

I've got a little help from a friend and it seems that the problem is a little bit moore complex than I've described above. The Column 'Pnr' contains some posts that are empty, hence the "Type mismatch".

So, with the SQL-question below, I got the result I wanted:

SELECT ID, Pnr, KortNr
FROM tbl_Person
WHERE IIf(ISNULL(Pnr),'xxx',Replace(Pnr,'-',''))='7407222954'

But unfortunatly it didn't stop there. My intention was to use this query from a ASP-page with an ADO-connection and this didn't work because the ADO-connection doesn't support the Replace-function so I had to get around that as well, but that is a completley different story! ;-)

Thank you all for your effort though!

//Daniel