Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Server Puzzle | Make all UPPER/LOWER case of string to Proper Case/Title Case

In this puzzle you have to given an input string. You have to make first letter of each work in upper case and all the remaining characters in lower case

Please check out the sample input and the expected output below-

Sample Input

‘7what the 789hell is 78 going on here dshf 78’

Expected Output

7what The 789hell Is 78 Going On Here Dshf 78

Rules/Restrictions

  • The solution should be should use SINGLE “SELECT” statement.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data


--

DECLARE @String VARCHAR(1000) =  '7what the 789hell is 78 going on here dshf 78'


--

Solution 1 | Number Table


--



DECLARE @InputString VARCHAR(1000) =  '7What the 789hell is 78 going on here dshf 78'
DECLARE @OutputString VARCHAR(1000) = ''

SELECT 
		@OutputString = @OutputString +
		CASE 
			WHEN Number = 1 THEN UPPER(SUBSTRING(@InputString,Number,1))
			WHEN SUBSTRING(@InputString,Number-1,1) = '' THEN UPPER(SUBSTRING(@InputString,Number,1))
		ELSE
			LOWER(SUBSTRING(@InputString,Number,1))
		END
FROM 
(
	SELECT DISTINCT Number
	FROM MASTER..SPT_VALUES N
	WHERE Number > 0 AND Number < LEN(@InputString)
)u

SELECT @OutputString Outputs

GO


--

Solution 2 | George Mastros | Modified Version 


--



DECLARE @String VARCHAR(1000) =  '7what the 789hell is 78 going on here dshf 78'
DECLARE @Position INT = -1

SELECT @String = STUFF(LOWER(@String),1,1,UPPER(LEFT(@String,1)))
SELECT @Position = PATINDEX('%[^A-Za-z0-9''][a-z]%',@String COLLATE Latin1_General_Bin)

WHILE @Position > 0
SELECT @String = STUFF(@String,@Position,2,UPPER(SUBSTRING(@String,@Position,2))),
@Position = PATINDEX('%[^A-Za-z0-9''][a-z]%',@String COLLATE Latin1_General_Bin)

SELECT @String


--

Solution 3 | Joe Celko 


--



declare @Foobar table(data varchar(1000))
insert into @Foobar
select '7this is only for testing' union all
select 'SEe if This workS well' union all
select 'TESTING DATA'
/*
Original data
*/
select * from @Foobar

/*
Modified data
*/

select
ltrim
(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
(LOWER (' '+data))
,' a',' A')
,' b',' B')
,' c',' C')
,' d',' D')
,' e',' E')
,' f',' F')
,' g',' G')
,' h',' H')
,' i',' I')
,' j',' J')
,' k',' K')
,' l',' L')
,' m',' M')
,' n',' N')
,' o',' O')
,' p',' P')
,' q',' Q')
,' r',' R')
,' s',' S')
,' t',' T')
,' u',' U')
,' v',' V')
,' w',' W')
,' x',' X')
,' y',' Y')
,' x',' Z')
) AS data
from @Foobar


--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com