WHERE condition in SELECT to retrieve records 1

[clo]

Hi!

I have a text field in a table and its name is no_id. An exemple of a no_id is: 'AB39MUO1'. An error occured and a user had entered the letters contained in the no_id in lower case (for example: 'ab39muo1').

I'd like to retrieve those records in the table but I don't know how to do this with a select statement:

Select no_id
FROM table_name
WHERE ???

I'd like to retrieve ONLY the no_id that are in lower case...

Any suggestion is welcome!
Thanks!

 

[jhall156]

you can use the ASCII() function and then test that value. JHall

 

[fluteplr]

Or if your table is not that big, why not just fix them.

update mytable replace no_id with upper(no_id)

 

[clo]

My tables are that big... I have 3 tables of at least 1 million records each. And I just can't "replace" them because records will be duplicated... Records with upper case already exists and are the good ones!

I am new to oracle and I just don't figure out how I can do this, even with the ASCII function... The ASCII function will test a character; how can I do to separate characters to test them?

Thanks...

 

[jhall156]

Are you working in Oracle? This is the SQL Server forum.
At any rate you can just replace all the characters with their UPPER if that's the end result you require. If you must identify them then what criteria are you going to use? For example would any lower-case character be enough to select the record or must they all be lower case? JHall

 

[clo]

I can't just replace the lower characters by upper because exactly the sames records already exist in the database and are the good ones...

no_id id is the key of the table; For example, let's say 'MICHEL01' is a no_id in my table. I don't want to have two persons named 'MICHEL01' (because the record is duplicated with 'michel01'). I have to delete the record in lower case because I have two occurences of the same person in the database....

In response to your question, any lower-case character will be enough to select the record...

Am I clear enough?
Sorry for my bad english, I am french speaking!
Thanks!

 

[jhall156]

In that case I would suggest that you identify duplicates by grouping their UPPER's and then correcting those and then UPPERing all the rest (since they are not dupes).


This query should satisfy the first part.

SELECT a.no_id, a.otherfields
FROM yourtable a INNER JOIN
(SELECT b.no_id FROM thesametable b
GROUP BY b.no_id
HAVING count(no_id) > 2) b
ON UPPER(a.no_id) = UPPER(b.no_id)

JHall

 

[jhall156]

Sorry if this is double posted. Typo.
In that case I would suggest that you identify duplicates


by grouping their UPPER's and then correcting those and then UPPERing all the rest (since they are not dupes).


This query should satisfy the first part.

SELECT a.no_id, a.otherfields
FROM yourtable a INNER JOIN
(SELECT b.no_id FROM thesametable b
GROUP BY b.no_id
HAVING count(no_id) > 1) b
ON UPPER(a.no_id) = UPPER(b.no_id)

JHall

 

[clo]

And what about this:

SELECT no_id
FROM myTable
WHERE no_id LIKE '%a%'

Is there is a way to replace the a letter by a range 'a-z' (lower)?

Sorry to ask you again and thank you for your helpful help =)

 

[jhall156]

Certainly,

WHERE PATINDEX('%[a-z]%',no_id)>0 JHall