SQL:How can I make the function that change all the uppercase to lowercase and lowercase to uppercase in a string

Question

How can I make the function that change all the uppercase to lowercase and lowercase to uppercase in a string e.g. "HelloW world" to "hELLOw WORLD"

Looks like homework to me. What have you tried so far? Post your code. — SS_DBA, Oct 06 '17 at 18:34
I'd implement the `translate` function and just use arguments for this purpose. — Gordon Linoff, Oct 06 '17 at 18:41
How come we always ask the proverbial & customary 'is it homework?' on the site? Just curious...i forgot. — Lee, Oct 06 '17 at 18:53
What version of SQL Server? Also just English alphabet or all alphabets? — Martin Smith, Oct 06 '17 at 18:57
@xixixixi, please always specify tag for version when you post a question. — FLICKER, Oct 06 '17 at 20:44

score 1 · Answer 1 · answered Oct 06 '17 at 18:50

1

You can write a function that loops through each character in the string one at a time, and if it is in the set of lower case characters, apply the UPPER function, otherwise apply the LOWER function, and build your function's output string.

answered Oct 06 '17 at 18:50

Tab Alleman

31,483
7
36
52

using the ascii to distinguish the upper and lower? – xixixixi Oct 06 '17 at 18:59
That's one way. Another is PATINDEX with a case-sensitive collation. (note that the range specification wouldn't work though. Have to type out all the letters of the alphabet in the pattern to be matched): https://stackoverflow.com/questions/4212110/range-wildcard-pattern-matching-behaviour-with-case-sensitive-collations – Tab Alleman Oct 06 '17 at 19:03
1

The range specification will work with a binary collation. – Martin Smith Oct 06 '17 at 19:07
OK, thank you so much – xixixixi Oct 06 '17 at 19:13

nitin.sharma0180 · Answer 2 · 2017-10-06T19:00:01.447

0

You can create a function where cursor will iterate and will pick/fetch the String value and if its a lower case you can use the SELECT UPPER('test string') to change the String to upper case .If the String value is coming as Upper Case you can use the function SELECT lOWER('test string') to convert to lowercase.

edited Oct 06 '17 at 19:00

answered Oct 06 '17 at 18:44

nitin.sharma0180

471
3
6

1

This is not OP asked for. look at his/her example – FLICKER Oct 06 '17 at 18:53

score 0 · Answer 3 · answered Oct 06 '17 at 19:58

0

You didn't state any version. For SQL Server 2017 you can use TRANSLATE as suggested by Gordon.

WITH T(S) AS
(
SELECT 'HelloW world'
)
SELECT TRANSLATE(S COLLATE Latin1_General_CS_AS, 
                'ABCDEFGHIJLKMNOPQRSTUVWXYZabcdefghijlkmnopqrstuvwxyz', 
                'abcdefghijlkmnopqrstuvwxyzABCDEFGHIJLKMNOPQRSTUVWXYZ')
FROM T

answered Oct 06 '17 at 19:58

Martin Smith

438,706
87
741
845

sorry I forgot about it, does it works on the 2016 server? – xixixixi Oct 06 '17 at 20:19
No. This is 2017 only – Martin Smith Oct 06 '17 at 20:20

SQL:How can I make the function that change all the uppercase to lowercase and lowercase to uppercase in a string

3 Answers3