CODEWITHSUNDEEP
sqlsql-servert-sql
aditya goud
aditya goud

Reputation: 25

sql query to bring last letter in a string to first letter position

I have a table name called 'Supervisor' from a table JobData in a MSSQL database. In this 'Supervisor' column the records are of the format "SPARKSL", "ADITYAG", "KENTONS", "DRISCOLLJ" and so on. I want to convert these records to lower case and bring the last letter to first letter.For example "SPARKSL" should be changed to the format "lsparks", "gaditya", "skentons" and so on and this format should be applied to all the remaining records.

Upvotes: 1

Views: 331

Answers (4)

Alan Burstein
Alan Burstein

Reputation: 7918

Just building off of Tim's answers (all three answers look fine)...

Yes - you will get an error is you pass it a blank or whitespace (one or more spaces only) because LEN(' ') = 0. 0-1 = -1. LEFT(<something>,-1) is not valid. To fix that you would change Tim's logic for new_sup to look like this:

ISNULL(LOWER(LEFT(Supervisor, NULLIF(LEN(Supervisor),0) - 1)),'') AS new_sup

The complete, updated solution would look like this: 

DECLARE @yourtable TABLE (Supervisor VARCHAR(100));
INSERT @yourtable(Supervisor)
VALUES ('SPARKSL'),('ADITYAG'),('KENTONS'),('DRISCOLLJ'),(' '),('');

WITH cte AS (
    SELECT *,
        LOWER(RIGHT(Supervisor, 1)) +
        ISNULL(LOWER(LEFT(Supervisor, NULLIF(LEN(Supervisor),0) - 1)),'') AS new_sup
    FROM @yourTable
)
SELECT *
FROM cte;

Upvotes: 1

Mark
Mark

Reputation: 740

You need to utilize the LOWER, RIGHT, SUBSTRING AND LEN commands. ie;

 UPDATE [Supervisor] SET [Supervisor] = LOWER(RIGHT([Supervisor], 1) + SUBSTRING([Supervisor],1, (LEN([Supervisor])-1)))

Upvotes: 0

Paweł Dyl
Paweł Dyl

Reputation: 9143

This should work:

WITH Demo AS
(
  SELECT X FROM (VALUES ('SPARKSL'),('ADITYAG'),('KENTONS')) T(X)
)
SELECT *, LOWER(SUBSTRING(X, LEN(X),1)+SUBSTRING(X,1,LEN(X)-1))
FROM Demo

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

Try this option:

WITH cte AS (
    SELECT *,
        LOWER(RIGHT(Supervisor, 1)) +
        LOWER(LEFT(Supervisor, LEN(Supervisor) - 1)) AS new_sup
    FROM yourTable
)

SELECT *
FROM cte;

enter image description here

Demo

If you instead want to actually update your supervisor column, then you may also use the above CTE:

UPDATE cte
SET Supervisor = new_sup;

Upvotes: 0

Related Questions