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

Advertisements