sql中的子字符串和case语句 [英] substring and case statement in sql

问题描述

如何在sql中使用子字符串和case语句?
谢谢.


使用子字符串，您将选择顾问专业的前两个缩写.这三个专业如下:
(数据库专家，数据库分析师，业务分析师)

我想知道查询以获取DS，DA，BA的结果.

谢谢您，

How to use substring and case statement in sql?
Thank you.


Using substring you will select the first 2 Initials of the consultant specialty. The specialties are these 3 as following:
(Database specialist, database analyst, business analyst)

I would like to know the query to get the results as DS, DA,BA.

Thank you,

推荐答案

首先，CASE在这里并不是很重要.

对于这些字符串中的每一个，您都需要将它们分成单词...即，根据空格将字符串分割成... ...有几种方法可以描述 ^ ]

获得结果后，您可以使用

Firstly, CASE is not really relevant here.

For each of these strings you will need to separate them into words ... i.e. SPLIT the string based on spaces ... there are several ways of doing that described here[^]

Once you have the results you can get the initial letter of each word using

select SUBSTRING(consultant_speciality, 1, 1)

获取每个单词的首字母，并将其与+运算符连接.
考虑到您呈现数据的方式，您可能还需要UPPER函数以确保其为大写字母

and concatenate them with the + operator.
Given the way you have presented the data you will probably also need the UPPER function to ensure it is in upper case

hi
子字符串在SQL Server中返回字符，二进制，文本和图像的一部分

声明@string varchar(30)=``Jignesh''
选择子字符串(@ string，0,4)

Case评估条件列表并返回多个可能的结果表达式之一
声明@data int = 1

在1时选择case @data，然后在选择"1"时在2时选择两个"，否则选择无效输入"
希望对您有帮助.

hi
Substring returns part of character, binary, text and image in SQL server

declare @string varchar (30)= ''Jignesh''
select substring(@string,0,4)

Case evaluates a list of conditions and returns one of multiple possible result expressions
declare @data int = 1

select case @data when 1 then ''one'' when 2 then ''two'' else ''not valid input'' end

hope this will help you.

一个基本的想法是在这里:

A basic idea is here:

CREATE TABLE #a (MyText VARCHAR(150))

INSERT INTO #a (MyText)
VALUES('Database specialist, database analyst, business analyst')

;WITH Words AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY MyText) AS RowNo, LEFT(MyText, CHARINDEX(',', MyText)-1) AS Word, LTRIM(RIGHT(MyText, LEN(MyText) - CHARINDEX(',', MyText))) AS Remainder
    FROM #a
    WHERE CHARINDEX(',', MyText)>0
    UNION ALL
    SELECT RowNo, LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS Word, LTRIM(RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder))) AS Remainder
    FROM Words
    WHERE CHARINDEX(',', Remainder)>0
    UNION ALL
    SELECT RowNo, LTRIM(Remainder) AS Word, NULL AS Remainder
    FROM Words
    WHERE CHARINDEX(',', Remainder)=0
)
SELECT RowNo, Word, UPPER(LEFT(Word,1) + SUBSTRING(Word,CHARINDEX(' ', Word)+1,1)) AS ShortWord
FROM Words


DROP TABLE #a

结果:

Result:

RowNo   Word                 ShortWord
1	Database specialist	DS
1	database analyst	DA
1	business analyst	BA

其余的事情您需要自己做.

The rest you need to do by yourself.

这篇关于sql中的子字符串和case语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！