我需要将一列中的数据截断为10个字符。但是,我不能有任何重复项,所以我希望任何重复项都以~1结尾表示第一个重复项,~2结尾表示第二个重复项。下面是我的一个例子:
Column
------
The ABC Company Inc.
The ABC Cooperative
XYZ Associates LLC.
我希望结果是:
Column
------
The ABC ~1
The ABC ~2
XYZ Associ
结尾不一定是~1或~2,我只需要一些东西,使它在截断后变得独特。截断后可能有3个或4个以上的重复项。
到目前为止,我只是手动截断和编辑表格:
update Table set Column = Left(Column, 10) where len(Column) > 10
首先,您关心的是前8个字符,而不是前10个字符,因为您需要为额外的数字预留插槽。
假设你的重复次数少于10次,你可以这样做:
with toupdate as (
select t.*,
row_number() over (partition by left(col, 8) order by (select null)) as seqnum,
count(*) over (partition by left(col, 8) ) as cnt
from t
update toupdate
set col = (case when cnt = 1 then left(col, 10)
else left(col, 8) + '~' + cast(seqnum as char(1));
同样的想法可以用于CCD_ 1。
Declare @Table Table (Column1 varchar(50))
Insert into @Table values
('The ABC Company Inc.'),
('The ABC Cooperative'),
('XYZ Associates LLC.')
Select NewColumn = Concat(substring(Column1,1,10),' ~',Row_Number() over (Partition By substring(Column1,1,10) Order by Column1))
From @Table
返回
NewColumn
The ABC Co ~1
The ABC Co ~2
XYZ Associ ~1
数字很嘈杂,所以我只在必要时添加它们:
select case when _r > 1
then Company + '~' + cast(_r as varchar(5))
else Company end as Company
from (
select Company
, ROW_NUMBER() over (partition by Company order by Company) as _r
from(
select left(Company, 10) as Company
from MyTable
) x
) y
order by Company
Company
--------------
The ABC Co
The ABC Co~2
XYZ Associ
假设您的表是COMPANY,字段是CompanyName。。。。。你将不得不花呢,但希望它能有所帮助。。
SELECT SUBSTRING( Q.Comp, 1, 5) + '~' + CONVERT(nvarchar(4), Row) as NewFieldValue FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY SUBSTRING( C.CompanyName, 1, 6) ORDER BY SUBSTRING( C.CompanyName, 1, 6)) AS Row,
SUBSTRING( C.CompanyName, 1, 6) as Comp
FROM COMPANY C
)Q
DECLARE @Table TABLE (Column1 varchar(50))
INSERT INTO @Table VALUES
('The ABC Company Inc.')
, ('The ABC Cooperative')
, ('XYZ Associates LLC.')
, ('Acme')
, ('Ten Char 123')
, ('Ten Char 132')
, ('Ten Char 231')
;WITH FLen
AS (
SELECT Column1, LEFT(LEFT(Column1,13) + SPACE(13),13) + CHAR(164) AS Column2
FROM @Table
)
,TenCharPD -- Includes possible duplicates
AS (
SELECT Column1, LEFT(Column2,8) +
RIGHT('0' + CAST (
(ASCII(SUBSTRING(Column2, 9,1)) +
ASCII(SUBSTRING(Column2,10,1)) +
ASCII(SUBSTRING(Column2,11,1)) +
ASCII(SUBSTRING(Column2,12,1)) +
ASCII(SUBSTRING(Column2,13,1)))%100
AS NVARCHAR(2)),2) AS Column2
FROM Flen
)
,CullPD
AS (
SELECT Column1, Column2,
ROW_NUMBER() OVER (PARTITION BY Column2 ORDER BY Column2) AS rowx
FROM TenCharPD
)
UPDATE t1
SET Column1 = LEFT(Column2,9) +
CASE rowx
WHEN 1 THEN RIGHT(Column2,1)
ELSE CHAR(rowx + CAST (RIGHT(Column2,1) AS INT) * 5 + 63)
END
FROM @Table t1
JOIN CullPD cpd
ON t1.Column1 = cpd.Column1
SELECT * FROM @Table