4

How can I convert a string like 'ThisIsTest' to 'This Is Test' based on proper case in T-SQL?

  • Input: 'ColumnA'
  • Output: 'Column A'
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2827587
  • 231
  • 4
  • 15
  • 1
    I'd use a CLR function and then use C# string tools for that... Strings aren't SQL Servers strength. – Dale K Dec 18 '18 at 04:05
  • 1
    I was thinking about how I'd do this in SQL and while the first things that come to mind are loops and recursive queries that iterate over each character of given string(s), the best way might just be 26 `REPLACE()` functions with case-sensitive collation... Not sure though. And that seems quite sad if it is the case. – ZLK Dec 18 '18 at 04:21
  • You might be able to use an iterative CTE and replace chars where the ASCII code is uppercase with a space plus the char? – Dale K Dec 18 '18 at 04:24
  • 1
    @DaleBurrell Yes, that's how you might do it in a CTE (or a function with a loop for that matter) but, since you have to check each character one by one, I think that might actually perform worse than simply 26 replace functions on a single string. Possibly. It's not something I have much interest in testing. I wouldn't do this in SQL if the list of strings was very large. – ZLK Dec 18 '18 at 04:42

1 Answers1

2

Try this.

DECLARE @Input VARCHAR(MAX)='ColumnA'

DECLARE @Regex AS VArCHAR(MAX), @Output AS VARCHAR(MAX)    

SET @Output = @Input
SET @Regex = '%[^ ][A-Z]%'

WHILE PATINDEX(@Regex collate Latin1_General_Bin, @Output) > 0
BEGIN
    SET @Output = Stuff(@Output, PatIndex(@Regex collate Latin1_General_Bin, @Output) + 1, 0, ' ')
END

SELECT @Output AS [Output]
Dumi
  • 1,414
  • 4
  • 21
  • 41