If this list contains 123456 characters and the number of lines is greater than 1 set ID the smallest line phone for the rest of the phone is set to repeat 123456
For example,
Raw data:
Id content phone
1 a123456
2 HJJ
3 222 ff
4 1234 nn
5 b123456
6 jjd123456bbj
After the implementation
Id content phone
1 a123456 123456
2 HJJ
3 222 ff
4 1234 nn
5 repeat b123456
6 repeat jjd123456bbj
Thank you very much!
CodePudding user response:
USE tempdb for
GO
IF OBJECT_ID (' dbo. [t]) IS NOT NULL
DROP TABLE dbo. [t]
GO
The CREATE TABLE dbo. [t] (
[id] INT
[content] NVARCHAR (30)
And [call] NVARCHAR (30)
)
GO
SET NOCOUNT ON
INSERT INTO dbo. [t] VALUES (N '1', N 'a123456', ')
INSERT INTO dbo. [t] VALUES (' 2 'N, N' HJJ ', ')
INSERT INTO dbo. [t] VALUES (N '3', N '222 ff', ')
INSERT INTO dbo. [t] VALUES (' 4 'N, N' 1234 nn ', ')
INSERT INTO dbo. [t] VALUES (N '5', N 'b123456', ')
INSERT INTO dbo. [t] VALUES (N '6', N 'jjd123456bbj', ')
GO
-- -- -- -- -- -- -- -- to test the above table and test data -- -- -- -- -- -- -- -- -- --
- 1. Using common table expressions to update data
; WITH cte AS (
The SELECT ROW_NUMBER () OVER (ORDER BY id) AS rids, *
The FROM t
WHERE t. [content] LIKE '% 123456%'
)
The UPDATE cte
SET the phone=CASE WHEN rids=1 THEN '123456' ELSE 'repeat' END
- 2. Query the updated data
SELECT * FROM t
/*
Id content phone
1 a123456 123456
2 HJJ
3 222 ff
4 1234 nn
5 repeat b123456
6 repeat jjd123456bbj
*/
CodePudding user response:
The CREATE TABLE # A (
ID INT,
Content VARCHAR (20),
Telephone VARCHAR (20)
)
# INSERT INTO A VALUES (1, 'a123456', ')
# INSERT INTO A VALUES (2, 'HJJ', ')
# INSERT INTO A VALUES (3, '222 ff', ')
# INSERT INTO A VALUES (4, '1234 nn', ')
# INSERT INTO A VALUES (5, 'b123456', ')
# INSERT INTO A VALUES (6, 'jjd123456bbj', ')
The SELECT, Anderson D a. content,
CASE
The WHEN a. content LIKE '% 123456%' AND THEN A.R N=1 '123456'
The WHEN a. content LIKE '% 123456%' AND A.R N!=1 THEN 'repeat'
The WHEN a. content LIKE '% 123456%' AND A.R N=1 THEN 'END call FROM (
SELECT *,
ROW_NUMBER () OVER (PARTITION BY (CASE WHEN content LIKE '% 123456%' THEN one ELSE 0 END) ORDER BY ID) RN
FROM A ORDER BY ID # A)
DROP TABLE # A