Click here to Skip to main content
15,944,731 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How to use substring and case statement in sql?
Thank you.

[Edit - OP information from "solution"]
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,
Posted
Updated 12-Mar-15 5:48am
v2
Comments
CHill60 12-Mar-15 11:34am    
What have you tried? Post the query you are trying to use or describe what you are trying to do
ZurdoDev 12-Mar-15 11:56am    
What is your question?
Maciej Los 13-Mar-15 3:05am    
What database: MySQL, SQL Server, PostgreeSQL, Oracle, MS Access?

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)
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
 
Share this answer
 
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.
 
Share this answer
 
A basic idea is here:
SQL
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.
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900