CODEWITHSUNDEEP
sqlsql-servert-sqluppercaselowercase
Kakashi Hatake
Kakashi Hatake

Reputation: 33

Change the casing of my letters in string using sql function

I want to convert my string into opposite casing in sql using function. can someone help me??

I am able to do the first letter of the string but not middle letters

http://www.sql-server-helper.com/functions/initcap.aspx

Example:

input: "hI mY Name IS Joe"

output: "Hi My nAME is jOE"

Upvotes: 2

Views: 510

Answers (3)

Madhukar
Madhukar

Reputation: 1242

Here is one of the methods to achieve this.

DECLARE @input NVARCHAR(30) = 'TesTINg PHRasE'
DECLARE @count INT = LEN(@input)
DECLARE @i INT = 1
DECLARE @val CHAR(1) 
DECLARE @final VARCHAR(20)

WHILE (@i <= @count)
BEGIN
SELECT @val = SUBSTRING(@input,@i,1)
SELECT @val = CASE WHEN ASCII(@val) BETWEEN 65 AND 90 THEN LOWER(@val) ELSE UPPER(@val) END
SET @final = CONCAT(@final,@val)
SET @i = @i + 1
END
SELECT @final AS Output

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

A completely set-based approach:

DECLARE @TheLinkTable TABLE(ID INT IDENTITY,YourText NVARCHAR(1000));
INSERT INTO @TheLinkTable VALUES('hI mY Name IS Joe');

The query:

WITH cte AS
(
    SELECT t.ID
          ,t.YourText
          ,A.Nmbr 
          ,C.SwitchedLetter
    FROM  @TheLinkTable t
    CROSS APPLY(SELECT TOP(LEN(t.YourText)) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values) A(Nmbr)
    CROSS APPLY(SELECT SUBSTRING(t.YourText,A.Nmbr,1)) B(TheLetter)
    CROSS APPLY(SELECT CASE WHEN TheLetter LIKE '[a-zA-Z]' 
                            THEN CHAR(ASCII(TheLetter) ^ 0x20)
                            ELSE CASE WHEN TheLetter=' '  THEN TheLetter END END) C(SwitchedLetter)
)
SELECT cte1.ID
      ,cte1.YourText
      ,(
        SELECT SwitchedLetter AS [*]
        FROM cte cte2
        WHERE cte2.ID=cte1.ID
        ORDER BY cte2.Nmbr
        FOR XML PATH(''),TYPE).value('.','nvarchar(max)'
       )
FROM cte  cte1
GROUP BY cte1.ID,cte1.YourText;

The idea in short:

Using a tally-on-the-fly (in this case a ROW_NUMBER() against any bigger set with a computed TOP-clause we get a running number from 1 to n where n is the count of letters.

The second APPLY will pick each letter separately.

The third apply will switch the casing of letters from a to z simply by XORing the binary representation and re-set the significant BIT. A blank is returned as is.

The following SELECT will group by the ID and use a correlated sub-query to reconcatenate the string.

Another set-based approach implies a recursive CTE:

WITH recCTE AS
(
    SELECT 1 AS position
          ,YourText 
          ,CAST(CASE WHEN ASCII(TheLetter) BETWEEN 65 AND 90 THEN LOWER(TheLetter)
                     ELSE CASE WHEN ASCII(TheLetter) BETWEEN 97 AND 122 THEN UPPER(TheLetter) END END AS NVARCHAR(MAX)) AS SwitchLetter
    FROM @TheLinkTable
    CROSS APPLY(SELECT SUBSTRING(YourText,1,1)) A(TheLetter)    

    UNION ALL
    SELECT r.position+1
          ,YourText 
          ,CONCAT(r.SwitchLetter
                  ,CASE WHEN ASCII(TheLetter) BETWEEN 65 AND 90 THEN LOWER(TheLetter)
                        ELSE CASE WHEN ASCII(TheLetter) BETWEEN 97 AND 122 THEN UPPER(TheLetter) 
                                  ELSE TheLetter END END) AS SwitchLetter
    FROM recCTE r
    CROSS APPLY(SELECT SUBSTRING(YourText,r.position+1,1)) A(TheLetter) 
    WHERE r.position<LEN(YourText)
)

SELECT * FROM recCte;

You must add a WHERE to pick the last one (e.g. LEN(SwitchLetter)=LEN(YourText)). I left it aside to show how it's working.

Upvotes: 3

Serkan Arslan
Serkan Arslan

Reputation: 13393

If your sql-server version support (sql-server-2017 and above), you can use TRANSLATE function

SELECT TRANSLATE ('hI mY Name IS Joe' COLLATE Latin1_general_CS_AS 
    ,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
    ,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')

Result:

 Hi My nAME is jOE

Upvotes: 3

Related Questions