Capitalizing only first letter of each Word in a Column using Query

Question

Capitalizing only first letter of each Word in a Column using Query

Guitar_player

SSCrazy

Points: 2991
More actions
October 10, 2013 at 2:00 am

#283673

Hi ,
I am Having a Column full of Capital Letter words like "ABC DEF GHI " , i want to convert that into "Abc Def Ghi"
Please help me out its very Urgent !! I have googled it but i am getting all Stored procedures i dont want Stored procedures .
I just want to use update command and finish it . Please help me out .
Thank you

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Guitar_player (10/10/2013)
Hi ,
I am Having a Column full of Capital Letter words like "ABC DEF GHI " , i want to convert that into "Abc Def Ghi"
Please help me out its very Urgent !! I have googled it but i am getting all Stored procedures i dont want Stored procedures .
I just want to use update command and finish it . Please help me out .
Thank you

Get the stored procedure.

Remove CREATE PROC from the first line.

Run the statement.

This solution (which uses a loop unfortunately) uses a function:

Function to Convert Text String to Title Case – Proper Case[/url]

Are functions acceptable?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Guitar_player SSCrazy Points: 2991 More actions · Answer 2

The reason is like i dont know how to use the functions here what i know is update command but in my DB there thousands of Records then how can i convert them with the above mentioned Function . Please guide me what to do ?

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Google for a tuturial on SQL Server functions and read it.

You are responsible for your data and before you let any update statement loose on it, you should understand what you are doing.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Guitar_player SSCrazy Points: 2991 More actions · Answer 4

Koen Verbeeck (10/10/2013)
Google for a tuturial on SQL Server functions and read it.
You are responsible for your data and before you let any update statement loose on it, you should understand what you are doing.

Yes i Agree , that Data is getting updated in the Capital format itself , so i came here to know what can be done

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

-- CREATE SOME SAMPLE DATA TO USE TO TEST THE SOLUTION

IF object_id('tempdb..#temp') IS NOT NULL

BEGIN;

DROP TABLE #temp;

END;

SELECT ID, VAL

INTO #temp

FROM (VALUES(1,'ABC DEF GHI JKL'),

(2,'DEF DEF GHI JKL'),

(3,'D DEF GHI JKL'))a(ID,VAL);

GO

-- BEGINNING OF SOLUTION

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DelimitedSplit8K]') AND xtype IN (N'FN', N'IF', N'TF'))

BEGIN;

DROP FUNCTION [dbo].[DelimitedSplit8K];

END;

GO

CREATE FUNCTION [dbo].[DelimitedSplit8K]

-- See http://www.sqlservercentral.com/articles/Tally+Table/72993/

--===== Define I/O parameters

(@pString VARCHAR(8000), @pDelimiter CHAR(1))

--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...

-- enough to cover VARCHAR(8000)

WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), --10E+1 or 10 rows

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front

-- for both a performance gain and prevention of accidental "overruns"

SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

),

cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)

SELECT 1 UNION ALL

SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter

),

cteLen(N1,L1) AS(--==== Return start and length (for use in substring)

SELECT s.N1,

ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)

FROM cteStart s

)

--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.

SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),

Item = SUBSTRING(@pString, l.N1, l.L1)

FROM cteLen l

;

GO

-- HOW DO WE USE THE SOLUTION ?

SELECT a.ID, a.VAL, ca.VAL

FROM #temp a

CROSS APPLY (SELECT STUFF((SELECT ' '+UPPER(SUBSTRING(c.Item,1,1))+LOWER(SUBSTRING(c.Item,2,LEN(c.Item)-1))

FROM #temp b

CROSS APPLY [dbo].[DelimitedSplit8K](b.VAL,' ') c

WHERE a.ID = b.ID

ORDER BY c.ItemNumber

FOR XML PATH(''), TYPE

).value('.','NVARCHAR(MAX)'),1,1,''

)

)ca(VAL);

-- RESULTS

ID VAL VAL

----------- --------------- -----------------

1 ABC DEF GHI JKL Abc Def Ghi Jkl

2 DEF DEF GHI JKL Def Def Ghi Jkl

3 D DEF GHI JKL D Def Ghi Jkl

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Guitar_player SSCrazy Points: 2991 More actions · Answer 6

Hi,

I have used the function to get the Result and i was able to get the answer and Sorry for the delay response .

Thank you.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

The question now is.... do you understand how it works?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)