Removing duplicate character from string

Question

Removing duplicate character from string

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

nadersam SSCertifiable Points: 6542 More actions · Answer 1

Lynn Pettis (10/19/2015)
Would something like this work?
declare @InputValue as varchar(50)='ttttest',
@Word varchar(50);
with e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
eTally(n) as (select row_number() over (order by (select null)) from e1 a cross join e1 b), -- don't need more than a 100 (actually 50)
pivotletters as (
select
n,
ascii(substring(@InputValue,n,1)) LetterValue,
substring(@InputValue,n,1) letters,
rn = n - row_number() over (partition by substring(@InputValue,n,1) order by n)
from eTally where n <= len(@InputValue)
)
select @Word = stuff(( select -- min(n), letters from pivotletters group by letters, rn order by min(n)
'' + letters
from
pivotletters
group by
letters, rn
order by
min(n)
for xml path(''),TYPE).value('.','varchar(50)'),1,0,'');
select @InputValue, @Word;

Thank you very much it worked.

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 2

Post removed. I made a mistake.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 3

Not enough coffee, yet. Made a Copy/Paste mistake.

Here's my shot at it. I'll do a performance test tonight (on my way to work just now).

--===== Build a long string

DECLARE @InputValue AS VARCHAR(8000)=REPLICATE(' ttttest daaaaaaaaaaataaaaaaa Abbott',400)

;

--========= Dedupe the letters

WITH

E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))e0(N))

,Tally(N) AS (SELECT TOP (DATALENGTH(@InputValue))

ROW_NUMBER() OVER (ORDER BY (SELECT a.N))

FROM E1 a, E1 b, E1 c, E1 d)

SELECT Word = ((SELECT SUBSTRING(@InputValue,t.N,1)

FROM Tally t

WHERE SUBSTRING(@InputValue,t.N ,1) COLLATE LATIN1_General_BIN

<> SUBSTRING(@InputValue,t.N+1,1) COLLATE LATIN1_General_BIN

ORDER BY t.N

FOR XML PATH(''),TYPE).value('.','VARCHAR(8000)'))

;

GO 5

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nadersam SSCertifiable Points: 6542 More actions · Answer 4

Jeff Moden (10/21/2015)
Not enough coffee, yet. Made a Copy/Paste mistake.
Here's my shot at it. I'll do a performance test tonight (on my way to work just now).
--===== Build a long string
DECLARE @InputValue AS VARCHAR(8000)=REPLICATE(' ttttest daaaaaaaaaaataaaaaaa Abbott',400)
;
--========= Dedupe the letters
WITH
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))e0(N))
,Tally(N) AS (SELECT TOP (DATALENGTH(@InputValue))
ROW_NUMBER() OVER (ORDER BY (SELECT a.N))
FROM E1 a, E1 b, E1 c, E1 d)
SELECT Word = ((SELECT SUBSTRING(@InputValue,t.N,1)
FROM Tally t
WHERE SUBSTRING(@InputValue,t.N ,1) COLLATE LATIN1_General_BIN
<> SUBSTRING(@InputValue,t.N+1,1) COLLATE LATIN1_General_BIN
ORDER BY t.N
FOR XML PATH(''),TYPE).value('.','VARCHAR(8000)'))
;
GO 5

Great thank you very much Jeff.