-1

I have a table with several records, some in lower case that are repeated later in upper case. example:

**Nombre **
Nicaragua
NICARAGUA
COLOMBIA
INGLATERRA

I need to eliminate only those that are written in lower case.

The normal delete or the 'like' do not work because they delete all the records that I specify with the 'where', please help

Dale K
  • 25,246
  • 15
  • 42
  • 71
fernan2
  • 1
  • 1
  • 2
    While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). – Yitzhak Khabinsky Nov 15 '22 at 21:42

1 Answers1

0

I'm not sure about how large or what exactly is in your dataset but you could simply remove the duplicates and update the data to upper case after. If you have more columns, you can add them to the partition where the pid column is generated. Not a perfect solution but it gets the job done!

create table    #test   (
                        col varchar(50)
                    )

insert into #test values
('USA')
,('usa')
,('nicaragua')
,('NICARAGUA')


-- Identifying duplicates. They'll have a PID of greater than 1
;with _dupes as
(
    select  ROW_NUMBER() over (partition by col order by col)   pid
            ,col
    from    #test
)

-- removing duplicates from data
delete  _dupes
where   pid > 1


-- updating data to uppercase
update  #test
set     col = UPPER(col)


select  *
from    #test


drop table  #test
PPJN
  • 332
  • 3
  • 13