I have data around 500 000. I need to update the data of First name and Second name to capital letters
My sample Data :
declare @T table(Firstname varchar(max),Secondname varchar(max))
insert into @T values ('ch bhaskar rao ','sridhar kolla ')
insert into @T values ('hemanth kumar','varun chenna-reddy')
insert into @T values ('mohan vara prasad','raju t d p durga raju')
insert into @T values ('police mutual','police mutual')
My Script :
;WITH CTE AS (
select (
select upper(T.N.value('.', 'char(1)')),
lower(stuff(T.N.value('.', 'varchar(max)'), 1, 1, ''))+(CASE WHEN RIGHT(T.N.value('.', 'varchar(max)'), 1)='-' THEN '' ELSE ' ' END)
from X.Secondname.nodes('/N') as T(N)
for xml path(''), type
).value('.', 'varchar(max)') As Secondname
from
(
select
cast('<N>'+replace(replace(replace(Secondname, ' ', '</N><N>'),' ', '</N><N>'),'-','-</N><N>') +'</N>' as xml) as Secondname
from @T
) as X
)
UPDATE T
SET
Secondname = C.Secondname
FROM
CTE C
INNER JOIN @T T
ON T.Secondname = C.Secondname
Select Secondname from @T
It is working fine when data is around 10 000 records but system getting hanged up when I'm trying to update huge records around 5 millions. How to update chunk by chunk like 10000 and again 10000, so that system will not burdened?
considering @t table is a physical table in your database.
This approach will have minimal logging.
;WITH CTE
AS (SELECT (SELECT Upper(T.N.value('.', 'char(1)')),
Lower(Stuff(T.N.value('.', 'varchar(max)'), 1, 1, '')) + ( CASE
WHEN RIGHT(T.N.value('.', 'varchar(max)'), 1) = '-' THEN ''
ELSE ' '
END )
FROM X.Secondname.nodes('/N') AS T(N)
FOR xml path(''), type).value('.', 'varchar(max)') AS Secondname
FROM (SELECT Cast('<N>'
+ Replace(Replace(Replace(Secondname, ' ', '</N><N>'), ' ', '</N><N>'), '-', '-</N><N>')
+ '</N>' AS XML) AS Secondname
FROM @T) AS X)
select ISNULL(C.Secondname,T.Secondname) into TEMP_TABLE
FROM CTE C
RIGHT JOIN @T T
ON T.Secondname = C.Secondname
recreate your index and constraints in new temp_table
Now rename the temp_table to old table name
exec sp_rename original_table, original_table_bck -- to rename the original table name to another name
exec sp_rename temp_table, original_table
You can update the table in chunks like mentioned below:
declare @i int = 2
declare @j int = 1
while @j > 0
begin
begin transaction
update top (@i) a
set a.firstname = upper(a.firstname),
a.secondname = upper(a.secondname)
from aa as a
where a.firstname collate latin1_general_cs_as <> upper(a.firstname)
set @j = @@rowcount
commit transaction
end
you can change the above code as per your needs. Let me know if this helps.
Why do you want to update the starting character of first name and last name in Database? You can handle it in CSS while you are displaying it by using
text-transform: capitalize;
Try this; this will help you in resolving the issue
Run the function below and then
SELECT dbo.InitCap
(Firstname
), dbo.InitCap
(Secondname
)
FROM @t;
IF OBJECT_ID('InitCap') IS NOT NULL
DROP FUNCTION InitCap;
GO
CREATE FUNCTION [dbo].[InitCap]
(
@InputString VARCHAR(4000)
)
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @Index INT;
DECLARE @Char CHAR(1);
DECLARE @PrevChar CHAR(1);
DECLARE @OutputString VARCHAR(255);
SET @OutputString = LOWER(@InputString);
SET @Index = 1;
WHILE @Index <= LEN(@InputString)
BEGIN
SET @Char = SUBSTRING(@InputString, @Index, 1);
SET @PrevChar = CASE
WHEN @Index = 1
THEN ' '
ELSE SUBSTRING(@InputString, @Index-1, 1)
END;
IF @PrevChar IN
(' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '('
)
BEGIN
IF @PrevChar != ''''
OR UPPER(@Char) != 'S'
SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char));
END;
SET @Index = @Index + 1;
END;
RETURN @OutputString;
END;
GO
