sql query to bring last letter in a string to firs

问题:

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.

回答1:

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

回答2:

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; 

回答3:

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;

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;

回答4:

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)))