Finding Duplicate Records

CREATE TABLE KeyLetter(
KeyNum int,
Letter char(1))
INSERT INTO KeyLetter VALUES(111111, 'a')
INSERT INTO KeyLetter VALUES(111111, 'b')
INSERT INTO KeyLetter VALUES(111111, 'c')
INSERT INTO KeyLetter VALUES(222222, 'a')
INSERT INTO KeyLetter VALUES(222222, 'b')
INSERT INTO KeyLetter VALUES(222222, 'c')
INSERT INTO KeyLetter VALUES(111111, 'a')
INSERT INTO KeyLetter VALUES(222222, 'a')
Given the above records, I have written the following to identify duplicate
records, but in my real life situation, I have a table with far more columns
to evaluate for duplication, and I was wondering if there is a better way to
identify duplicates such as this, without performing concatenation?
SELECT Cast(KeyNum as varchar(6)) + Letter),
COUNT(Cast(KeyNum as varchar(6)) + Letter))
FROM KeyLetter
GROUP BY Cast(KeyNum as varchar(6)) + Letter)
HAVING COUNT(Cast(KeyNum as varchar(6)) + Letter)) > 1
--
Message posted via http://www.sqlmonster.comUmm, anything wrong with this:
select KeyNum, Letter, count(*)
from KeyLetter
group by KeyNum, Letter
having count(*)>1
MC
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7989a4847c279@.uwe...
> CREATE TABLE KeyLetter(
> KeyNum int,
> Letter char(1))
> INSERT INTO KeyLetter VALUES(111111, 'a')
> INSERT INTO KeyLetter VALUES(111111, 'b')
> INSERT INTO KeyLetter VALUES(111111, 'c')
> INSERT INTO KeyLetter VALUES(222222, 'a')
> INSERT INTO KeyLetter VALUES(222222, 'b')
> INSERT INTO KeyLetter VALUES(222222, 'c')
> INSERT INTO KeyLetter VALUES(111111, 'a')
> INSERT INTO KeyLetter VALUES(222222, 'a')
> Given the above records, I have written the following to identify
> duplicate
> records, but in my real life situation, I have a table with far more
> columns
> to evaluate for duplication, and I was wondering if there is a better way
> to
> identify duplicates such as this, without performing concatenation?
> SELECT Cast(KeyNum as varchar(6)) + Letter),
> COUNT(Cast(KeyNum as varchar(6)) + Letter))
> FROM KeyLetter
> GROUP BY Cast(KeyNum as varchar(6)) + Letter)
> HAVING COUNT(Cast(KeyNum as varchar(6)) + Letter)) > 1
> --
> Message posted via http://www.sqlmonster.com
>

Find UNICODE Data

How can you find UNICODE date within char & varchar fields in table.
I am in the midst of a migration and need to evaluate all the fields that
may have €
chinese, french and thus require more char space in the new structure.
This is a matter of great urgency so any help greatly appreciated.
Thanks
marcIf the fields are not a Unicode data type, then there is no Unicode data in
them. If the characters are not supported by the code page of the field
(defined by its by collation), they are lost (will show up as '?'). If they
are supported by the code page, you can search for them like you normally
would, or by using full-text search.
David Barber [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:699A4648-85DC-4B5F-A876-6B223CD4A5DE@.microsoft.com...
> How can you find UNICODE date within char & varchar fields in table.
> I am in the midst of a migration and need to evaluate all the fields that
> may have ?
> chinese, french and thus require more char space in the new structure.
> This is a matter of great urgency so any help greatly appreciated.
> Thanks
> marc

Find string between 2 characters and insert in different column

I have data in a char column like this:
[1]
[2]
[3]
etc
[100]
[101]
etc
[1000]
etc
I want to select everything between the brackets and insert into a different
column.
Any help please.How about
SELECT REPLACE(REPLACE(YourCol,'[',''),']','')
FROM YourTable
Andrew J. Kelly SQL MVP
"Terri" <terri@.cybernets.com> wrote in message
news:d4p3vr$l0e$1@.reader2.nmix.net...
>I have data in a char column like this:
> [1]
> [2]
> [3]
> etc
> [100]
> [101]
> etc
> [1000]
> etc
> I want to select everything between the brackets and insert into a
> different
> column.
> Any help please.
>
>|||UPDATE Floob
SET new_foo =
REPLACE ( REPLACE (foobar, '[', ''), ']' '');
or you can do this in a VIEW or computed column.|||As a general approach, you can use the following expression:
SUBSTRING( @.s, CHARINDEX( '[', @.s ) + 1,
CHARINDEX( ']', @.s,
CHARINDEX( '[', @.s ) ) -
CHARINDEX( '[', @.s ) - 1 )
Anith|||Hi Terri,
Try this query...
SELECT Substring(YouCol,2,len(youCol)-2) FROM YourTable
Swami.
"Terri" wrote:

> I have data in a char column like this:
> [1]
> [2]
> [3]
> etc
> [100]
> [101]
> etc
> [1000]
> etc
> I want to select everything between the brackets and insert into a differe
nt
> column.
> Any help please.
>
>

Find last char index

Hi
i have column with this values :
aa-bb-cc-dd
ss-aa
dd-aa-ee
How can i find last '-' char index
i need retrieve this result :
dd
aa
ee
Thank youSELECT RIGHT(col,CHARINDEX('-',REVERSE(col))-1)
FROM YourTable
David Portas
SQL Server MVP
--|||The las '-' is the first of the reverse. Try:
select
reverse(left(reverse(colA), charindex('-', reverse(colA)) - 1))
from
(
select 'aa-bb-cc-dd'
union all
select 'ss-aa'
union all
select 'dd-aa-ee'
) as t(colA)
go
-- if max number of dashes allowed is 4, then
select
parsename(replace(colA, '-', '.'), 1)
from
(
select 'aa-bb-cc-dd'
union all
select 'ss-aa'
union all
select 'dd-aa-ee'
) as t(colA)
go
AMB
"Itzik" wrote:

> Hi
> i have column with this values :
> aa-bb-cc-dd
> ss-aa
> dd-aa-ee
> How can i find last '-' char index
> i need retrieve this result :
> dd
> aa
> ee
> Thank you
>
>