sql查询将字符串中的最后一个字母带到第一个字母位置 [英] sql query to bring last letter in a string to first letter position

问题描述

我在 MSSQL 数据库中的 JobData 表中有一个名为Supervisor"的表.在此主管"列中，记录的格式为SPARKSL"、ADITYAG"、KENTONS"、DRISCOLLJ"等.我想将这些记录转换为小写并将最后一个字母变为第一个字母.例如SPARKSL"应更改为lsparks"、gaditya"、skentons"等格式，并且这种格式应应用于所有剩余的记录.

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.

推荐答案

只是基于 Tim 的答案(所有三个答案看起来都不错)...

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

是的 - 如果您传递一个空格或空格(仅限一个或多个空格)，您将收到错误消息，因为 LEN(' ') = 0.0-1 = -1.LEFT(,-1) 无效.要解决此问题，您可以将 new_sup 的 Tim 逻辑更改为如下所示:

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; 

这篇关于sql查询将字符串中的最后一个字母带到第一个字母位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！