SQL 查询语句: 字符串首字母大写 All In One

SQL 查询语句: 字符串首字母大写 All In One

SQL 用户名第一个字符转换成大写的，其余字符都转换成小写的

SQL 字符串首字母大写

demo

字符串拼接

# CONCAT ✅

SELECT user_id,
CONCAT(UCASE(LEFT(name, 1)), LCASE(SUBSTRING(name, 2, LENGTH(name)))) AS name
FROM Users
ORDER BY user_id

# + ❌

SELECT user_id,
(UCASE(LEFT(Users.name, 1)) + LCASE(SUBSTRING(Users.name, 2, LENGTH(Users.name)))) AS name
FROM Users
ORDER BY user_id

MySQL

# Write your MySQL query statement below

# ✅
SELECT user_id,
CONCAT(UCASE(LEFT(name, 1)), LCASE(SUBSTRING(name, 2, LENGTH(name)))) AS name
FROM Users
ORDER BY user_id

# ✅
# SELECT user_id,
# CONCAT(UCASE(SUBSTRING(name, 1, 1)), LCASE(SUBSTRING(name, 2, LENGTH(name)))) AS name
# FROM Users
# ORDER BY user_id

# ✅
# SELECT user_id,
# CONCAT(UCASE(LEFT(name, 1)), LCASE(SUBSTR(name, 2, LENGTH(name)))) AS name
# FROM Users
# ORDER BY user_id

# ✅
# SELECT user_id,
# CONCAT(UPPER(LEFT(name, 1)), LOWER(SUBSTR(name, 2, LENGTH(name)))) AS name
# FROM Users
# ORDER BY user_id

# ✅
# SELECT user_id,
# CONCAT(UPPER(LEFT(name, 1)), LOWER(RIGHT(name, LENGTH(name) - 1))) AS name
# FROM Users
# ORDER BY user_id


# ❌
# SELECT user_id,
# (UCASE(LEFT(Users.name, 1)) + LCASE(SUBSTRING(Users.name, 2, LENGTH(Users.name)))) AS name
# FROM Users
# ORDER BY user_id


# ❌
# UPDATE Users
# SET name = UCASE(LEFT(name, 1)) + LCASE(SUBSTRING(name, 2))
# ORDER BY user_id

# ❌
# UPDATE Users
# SET name = UPPER(LEFT(name, 1)) + LOWER(SUBSTRING(name, 2, LENGTH(name)))
# ORDER BY user_id

# LEFT / RIGHT / SUBSTRING  / LENGTH

#  CONCAT

# INNER JOIN

# ON

# LIKE
# LEFT(,)
# RIGHT(,)
# IF(,,)
# MOD()
# SET
#  ASC/DESC

https://leetcode.cn/problems/fix-names-in-a-table/

LEN vs LENGTH

LEN() 函数, 返回文本字段中值的长度

SQL 中函数为 LEN()

SELECT LEN(column_name) FROM table_name;

MySQL 中函数为 LENGTH():

SELECT LENGTH(column_name) FROM table_name;

MySQL 函数

MySQL 内置的函数

MySQL 字符串函数

1. UCASE & LCASE

2. SUBSTRING / SUBSTR

3. LENGTH

4. LEFT & RIGHT

函数名称	说明	例子
STRCMP(s1,s2)	比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1	比较字符串是否相等：\nSELECT STRCMP("xgqfrms", "webfullsatck");
SUBSTR(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	从字符串中的第 2 个位置截取 3个字符：SELECT SUBSTR("xgqfrms", 2, 3) AS ExtractString;
SUBSTRING(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串，等同于 SUBSTR(s, start, length)	从字符串中的第 2 个位置截取 3个 字符：SELECT SUBSTRING("xgqfrms", 2, 3) AS ExtractString;
UCASE(s)	将字符串转换为大写	将字符串转换为大写：SELECT UCASE("xgqfrms");
UPPER(s)	将字符串转换为大写	将字符串转换为大写：SELECT UPPER("xgqfrms");
LCASE(s)	将字符串转换为小写	将字符串转换为小写：SELECT LCASE("xgqfrms");
LOWER(s)	将字符串转换为小写	将字符串转换为小写：SELECT LOWER("xgqfrms");
LEFT(s,n)	返回字符串 s 的前 n 个字符	返回字符串中的前两个字符： SELECT LEFT('xgqfrms',2)
RIGHT(s,n)	返回字符串 s 的后 n 个字符	返回字符串的后两个字符：SELECT RIGHT('xgqfrms', 2)
CONCAT(s1,s2...sn)	把 s1, s2, ..., sn,等多个字符串合并为一个字符串	合并多个字符串：SELECT CONCAT("MySQL ", "Gooogle ", "Facebook") AS ConcatenatedString;

...

https://www.runoob.com/mysql/mysql-functions.html

CONCAT & AS

SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info
FROM Websites;

SELECT name AS n, country AS c
FROM Websites;

https://www.runoob.com/?s=sql+concat

https://www.runoob.com/sql/sql-alias.html

MySQL 函数

CONCAT(s1,s2...sn) 字符串 s1,s2 等多个字符串合并为一个字符串

# 合并多个字符串
SELECT CONCAT("SQL ", "Gooogle ", "Facebook") AS ConcatenatedString;

https://www.runoob.com/mysql/mysql-functions.html

MySQL 存储过程

https://www.runoob.com/w3cnote/mysql-stored-procedure.html

refs

https://www.runoob.com/sql/sql-func-len.html

https://www.runoob.com/sql/sql-func-ucase.html
https://www.runoob.com/sql/sql-func-lcase.html

---

---

©xgqfrms 2012-2020

www.cnblogs.com/xgqfrms 发布文章使用：只允许注册用户才可以访问！

原创文章，版权所有©️xgqfrms, 禁止转载 🈲️，侵权必究⚠️！

---