CODEWITHSUNDEEP
sqlsql-server
gaurav b
gaurav b

Reputation: 93

I want to update values of a column in a table to Title case

I have a table in the database in which there is column name present in it and I want update the values in that name field to Title case.

Example : "abc asd" to "Abc Asd"

I want to know what is the best way of doing this using SQL, considering that there are millions of records in the table.

Upvotes: 0

Views: 1176

Answers (3)

Pooja
Pooja

Reputation: 1

I had Customer_Name stored in upper case. Example: MUHAMMED MACINTYRE

I had to return it in title-case/proper-case. Example: Muhammed Macintyre

I used the following code in MySql.

CHECK HERE

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

Data Hygiene is a constant battle. Proper case is never as simple as one may think. There are numerous variations and inconsistencies when consuming data from the wild.

Here is a function which can be expanded if necessary.

Full Disclosure: There are many other more performant functions, but they tend to have an oversimplified approach.

Example

Declare @YourTable table (SomeCol varchar(100))
Insert Into @YourTable values
('old mcdonald'),
('dr. Langdon ,dds'),
('b&o railroad'),
('john-m-smith'),
('CARSON/jACOBS'),
('jAmes o''neil')

 Select *
       ,ProperCase = [dbo].[svf-Str-Proper](SomeCol)
 From @YourTable

Returns

SomeCol             ProperCase
old mcdonald        Old McDonald
dr. Langdon ,dds    Dr. Langdon ,DDS
b&o railroad        B&O Railroad
john-m-smith        John-M-Smith
CARSON/jACOBS       Carson/Jacobs
jAmes o'neil        James O'Neil

The UDF if Interested

CREATE FUNCTION [dbo].[svf-Str-Proper] (@S varchar(max))
Returns varchar(max)
As
Begin
    Set @S = ' '+Replace(Replace(Lower(@S),'   ',' '),'  ',' ')+' '
    ;with cte1 as (Select * From (Values(' '),('-'),('/'),('\'),('['),('{'),('('),('.'),(','),('&'),(' Mc'),(' O''')) A(P))
         ,cte2 as (Select * From (Values('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M')
                                       ,('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z')
                                       ,('LLC'),('PhD'),('MD'),('DDS')
                                 ) A(S))
         ,cte3 as (Select F = Lower(A.P+B.S),T = A.P+B.S From cte1 A Cross Join cte2 B ) 
    Select @S = replace(@S,F,T) From cte3
    Return rtrim(ltrim(@S))
End
-- Syntax :  Select [dbo].[svf-Str-Proper]('old mcdonald phd,dds llc b&o railroad')

Upvotes: 5

S3S
S3S

Reputation: 25112

For basic examples, using a tally table string splitter like Jeff Moden's, or what ever split function you want in place of the DelimitedSplit8K() below, you can split them on the spaces and then stuff them back together after fixing the proper case. Note, using this type of split function is faster than RBAR (while loop, etc) methods commonly seen.

DEMO

declare @table table (v varchar(4000))
insert into @table
values
('abc abc abc'),
('Def abc ABC'),
('qrs ABC abc'),
('tuv'),
(' this is an odd-string# that3 has some 435 in it. It has leading and trailing spaces? ')

select distinct
    *
    ,STUFF((
          SELECT ' ' + upper(left(lower(rtrim(ltrim(x.Item))),1)) + right(lower(rtrim(ltrim(x.Item))),len(rtrim(ltrim(x.Item))) - 1)
          from @table t
          cross apply DelimitedSplit8K(rtrim(ltrim(v)),' ') x
          where t.v = b.v
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') 
from @table b

Upvotes: 1

Related Questions