SQL-Ex blog
Как перевести первую букву каждого слова предложения в верхний регистр?
Добавил smois on Суббота, 13 апреля. 2019
Эта задача возникла как осмысленный пример для демонстрации использования скалярных функций, табличных переменных и новых возможностей языка T-SQL. Итак, требуется создать функцию, которая будет делать первую букву каждого слова заглавной, а остальные — строчными.
- Слова разделяются единичными пробелами.
- Количество слов произвольно.
- Разбиваем фразу на отдельные слова, которые помещаем в табличную переменную.
- Делаем первую букву каждого слова прописной.
- Собираем строку из слов, соблюдая порядок, с помощью функции STRING_AGG.
create or alter function firstBigLetter(@name varchar(max))
returns varchar(max)
as
begin
declare @i int = 1, @next int = 1;
declare @t table(id int identity primary key, word varchar(100))
-- Пока подумаем, зачем нам identity?
while @next > 0
begin
select @next=charindex(' ', @name, @i);
if @next=0 -- больше пробелов нет, берем последнее слово
insert into @t(word) select substring(@name, @i, len(@name))
else -- берем очередное слово между @i и @next
insert into @t(word) select substring(@name, @i, @next-@i)
set @i=@next + 1; -- начало следующего слова
end
--Выполняем требуемое преобразование слов
update @t set word= upper(left(word, 1)) + lower(substring(word, 2, len(word)))
-- Сборка и возврат
return(select string_agg(word, ' ')
WITHIN GROUP (ORDER BY id) -- вот зачем нам Identity!
from @t)
end;
go
-- Вызов
select dbo.firstBigLetter('еСли кто-то кое-ГДе у наС порой');
--Результат
Если Кто-то Кое-где У Нас Порой
Пояснения.
- Конструкция CREATE OR ALTER FUNCTION появилась в SQL Server 2017 как аналог CREATE OR REPLACE FUNCTION в PostgreSQL. Теперь не нужно напрягать память, чтобы не забыть поменять ALTER на CREATE после отладки.
- Столбец Identity задает нам порядок, в котором следуют слова в предложении. Поэтому при сборке мы используем сортировку именно по этому столбцу — WITHIN GROUP (ORDER BY id).
- Естественно, можно было не выполнять разбивку на слова, а, например, использовать рекурсивный запрос, но я преследовал тут вполне определенные учебные цели. Если кто-то решит оценить производительность различных алгоритмов, можете поделиться результатами в комментариях.
Функция STRING_AGG, которая использовалась при сборке предложения, имеет обратную функцию STRING_SPLIT (обе эти функции появились в SQL Server 2017), которая как раз выполняет разбивку символьной строки, формируя одностолбцовую таблицу. Использование этой функции напрашивается для решения нашей задачи. Однако, согласно документации, она не гарантирует порядка. В моих примерах строка восстанавливалась правильно, поэтому я приведу и это решение в качестве примера использования функции. Но если вы решите применять её, то делайте это на свой страх и риск.
create or alter function firstBigLetterWithoutOrder(@name varchar(max))
returns varchar(max)
as
begin
return(select string_agg(upper(left(value,1))+lower(substring(value,2,len(value))),' ') from
(select value from STRING_SPLIT(@name,' ')) X)
end
go
--Вызов
select dbo.firstBigLetterWithoutOrder('еСли кто-то кое-ГДе у наС порой');
SUBSTRING (Transact-SQL)
Возвращает часть символьного, двоичного, текстового или графического выражения в SQL Server.
Синтаксис
SUBSTRING ( expression, start, length ) Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
выражение
Выражение типа character, binary, text, ntext или image.
Начало
Целое число или выражение типа bigint, указывающее начальную позицию возвращаемых символов. (Нумерация начинается с 1, то есть первый символ в выражении имеет позицию 1.) Если аргумент start имеет значение меньше 1, то возвращаемое выражение начинается с первого символа, который указан в аргументе expression. В этом случае количество возвращаемых символов является наибольшим значением либо суммы start + length– 1, либо 0. Если значение start больше количества символов в выражении значения, возвращается выражение нулевой длины.
length
Положительное целое число или выражение типа bigint, указывающее количество символов выражения expression, которое будет возвращено. Если значение length отрицательно, возникает ошибка и выполнение инструкции прерывается. Если сумма start и length больше количества символов в expression, то возвращается целочисленное выражение значения, начинающееся со значения start.
Типы возвращаемых данных
Возвращает символьные данные, если expression имеет один из поддерживаемых символьных типов данных. Возвращает двоичные данные, если аргумент expression имеет один из поддерживаемых двоичных типов данных. Возвращенная строка имеет тот же самый тип, как и заданное выражение. Исключения указаны в таблице.
| Заданное выражение | Возвращаемый тип |
|---|---|
| char/varchar/text | varchar |
| nchar/nvarchar/ntext | nvarchar |
| binary/varbinary/image | varbinary |
Замечания
Значения start и length должны быть указаны в виде количества символов для типов данных ntext, char или varchar и байтов для типов данных text, image, binary или varbinary.
Аргумент expression должен иметь тип varchar(max) или varbinary(max), если аргумент start или length содержит значение, превышающее 2 147 483 647.
Дополнительные символы (суррогатные пары)
При использовании параметров сортировки дополнительных символов (SC) и start, и length обрабатывают каждую суррогатную пару в expression как один символ. Дополнительные сведения см. в статье Collation and Unicode Support.
Примеры
А. Использование SUBSTRING с символьной строкой
Следующий пример показывает, как получить часть символьной строки. Из таблицы sys.databases этот запрос возвращает имена системных баз данных в первом столбце, первую букву имени базы данных во втором столбце и третий и четвертый символы в последнем столбце.
SELECT name, SUBSTRING(name, 1, 1) AS Initial , SUBSTRING(name, 3, 2) AS ThirdAndFourthCharacters FROM sys.databases WHERE database_id < 5; | name | Начальное | ThirdAndFourthCharacters |
|---|---|---|
| master | m | st |
| tempdb | t | mp |
| модель | m | de |
| msdb | m | db |
Далее показано, как можно вывести второй, третий и четвертый символ строковой константы abcdef .
SELECT x = SUBSTRING('abcdef', 2, 3); x ---------- bcd (1 row(s) affected) B. Использование SUBSTRING с данными типа text, ntext или image
Для выполнения приведенных ниже примеров необходимо установить базу данных pubs.
В приведенном ниже примере показано, как вернуть первые 10 символов из каждого столбца данных text и image в таблице pub_info базы данных pubs . Данные text возвращаются как varchar, а данные image — как varbinary.
USE pubs; SELECT pub_id, SUBSTRING(logo, 1, 10) AS logo, SUBSTRING(pr_info, 1, 10) AS pr_info FROM pub_info WHERE pub_id = '1756'; pub_id logo pr_info ------ ---------------------- ---------- 1756 0x474946383961E3002500 This is sa (1 row(s) affected) В приведенном ниже примере показано влияние функции SUBSTRING на данные типов text и ntext. Во-первых, пример создает новую таблицу в базе данных pubs под именем npub_info . Во-вторых, пример создает столбец pr_info в таблице npub_info из первых 80 символов столбца pub_info.pr_info и добавляет ü в качестве первого символа. Наконец, с помощью предложения INNER JOIN извлекаются все идентификационные номера издателей, а также обработанные функцией SUBSTRING значения столбцов типа text и ntext со сведениями об издателях.
IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'npub_info') DROP TABLE npub_info; GO -- Create npub_info table in pubs database. Borrowed from instpubs.sql. USE pubs; GO CREATE TABLE npub_info ( pub_id CHAR(4) NOT NULL REFERENCES publishers(pub_id) CONSTRAINT UPKCL_npubinfo PRIMARY KEY CLUSTERED, pr_info ntext NULL ); GO -- Fill the pr_info column in npub_info with international data. RAISERROR('Now at the inserts to pub_info. ',0,1); GO INSERT npub_info VALUES('0736', N'üThis is sample text data for New Moon Books, publisher 0736 in the pubs database') ,('0877', N'üThis is sample text data for Binnet & Hardley, publisher 0877 in the pubs databa') ,('1389', N'üThis is sample text data for Algodata Infosystems, publisher 1389 in the pubs da') ,('9952', N'üThis is sample text data for Scootney Books, publisher 9952 in the pubs database') ,('1622', N'üThis is sample text data for Five Lakes Publishing, publisher 1622 in the pubs d') ,('1756', N'üThis is sample text data for Ramona Publishers, publisher 1756 in the pubs datab') ,('9901', N'üThis is sample text data for GGG&G, publisher 9901 in the pubs database. GGG&G i') ,('9999', N'üThis is sample text data for Lucerne Publishing, publisher 9999 in the pubs data'); GO -- Join between npub_info and pub_info on pub_id. SELECT pr.pub_id, SUBSTRING(pr.pr_info, 1, 35) AS pr_info, SUBSTRING(npr.pr_info, 1, 35) AS npr_info FROM pub_info pr INNER JOIN npub_info npr ON pr.pub_id = npr.pub_id ORDER BY pr.pub_id ASC; Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)
C. Использование SUBSTRING с символьной строкой
Следующий пример показывает, как получить часть символьной строки. Из таблицы dbo.DimEmployee данный запрос возвращает фамилию в одном столбце и первую букву имени в другом.
-- Uses AdventureWorks SELECT LastName, SUBSTRING(FirstName, 1, 1) AS Initial FROM dbo.DimEmployee WHERE LastName LIKE 'Bar%' ORDER BY LastName; LastName Initial -------------------- ------- Barbariol A Barber D Barreto de Mattos P В приведенном ниже примере показано, как получить второй, третий и четвертый символы строковой константы abcdef .
USE ssawPDW; SELECT TOP 1 SUBSTRING('abcdef', 2, 3) AS x FROM dbo.DimCustomer; x ----- bcd Рекурсивные СТЕ стр. 2
Преобразовать текст в столбце таблицы таким образом, чтобы каждое слово начиналось с заглавной буквы.
Вот пример данных и требуемый результат:
За небольшим числом исключений (среди которых можно упомянуть аббревиатуры и инициалы) можно считать, что слову внутри текста предшествует пробел. Это можно использовать в качестве критерия поиска нужных нам элементов текста. Предлагаю реализовать такой достаточно примитивный алгоритм:
1. Первую букву текста делаем прописной, а остальные - строчными.
2. Затем каждую конструкцию "пробел+буква" переводим в верхний регистр.
С первым пунктом алгоритма все просто:
Консоль
Выполнить
Форум пользователей MySQL
Добрый день! У меня вопрос :существует ли sql запрос для того что бы изменить регистр первой буквы в столбике на заглавную?
Если у кого есть подобный опыт пожалуйста подскажите для латинских букв и для русских! Спасибо!
#2 31.01.2012 15:04:28
vasya Архат Откуда: Орел Зарегистрирован: 07.03.2007 Сообщений: 5823
Re: Изменить регистр первой буквы на заглавную в столбце
update `таблица` set `столбец`= concat ( upper ( left ( `столбец`, 1 ) ) ,substr ( `столбец`, 2 ) ) ;
#3 31.01.2012 15:25:43
viktor30 Завсегдатай Зарегистрирован: 30.01.2012 Сообщений: 27
Re: Изменить регистр первой буквы на заглавную в столбце
vasya написал:
update `таблица` set `столбец`= concat ( upper ( left ( `столбец`, 1 ) ) ,substr ( `столбец`, 2 ) ) ;
Спасибо большое ! А еще посоветуйте такой вопрос : как убрать пробел - он как первый знак (2838, ' diamonds diamond'),
что бы потом поменять регистр первой буквы
#4 31.01.2012 15:31:08
vasya Архат Откуда: Орел Зарегистрирован: 07.03.2007 Сообщений: 5823
Re: Изменить регистр первой буквы на заглавную в столбце
А документацию по ссылке из идеологических или религиозных соображений не смотрите?
#5 31.01.2012 15:34:16
viktor30 Завсегдатай Зарегистрирован: 30.01.2012 Сообщений: 27
Re: Изменить регистр первой буквы на заглавную в столбце
vasya написал:
А документацию по ссылке из идеологических или религиозных соображений не смотрите?
Все сделал! Спасибо за советы! Уже второй раз нахожу помощь на этом форуме! Спасибо громадное!