0

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.

Daniel E.
  • 2,440
  • 1
  • 14
  • 24
gaurav b
  • 63
  • 5
  • 2
    This has been asked and sort of answered hundreds if not thousands of times. There are so many caveats to title case that using t-sql is not really a great approach. This requires regular expressions and a whole myriad of exceptions. – Sean Lange Jan 17 '19 at 16:10
  • 4
    I'd suggest that you do such sort of thing on the presentation layer. That will be faster. – DhruvJoshi Jan 17 '19 at 16:12
  • Possible duplicate of [SQL Server: Make all UPPER case to Proper Case/Title Case](https://stackoverflow.com/q/230138/1260204) – Igor Jan 17 '19 at 16:17
  • Or do some google searching / reading https://www.google.com/search?q=sql+varchar+to+title+case+site:stackoverflow.com and give it a shot. – Igor Jan 17 '19 at 16:18

3 Answers3

5

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')
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • 1
    yeah add this one to my toolbox, gracias. – S3S Jan 17 '19 at 16:37
  • @scsimon Thanks for the grin. In my world, the data in cte1 and cte2 reside in a generic mapping table... easier to maintain and expand. I just placed the content in a CTE for portability. – John Cappelletti Jan 17 '19 at 16:40
1

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
S3S
  • 24,809
  • 5
  • 26
  • 45
0

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

Pooja
  • 1
  • 1