Concatenate two columns based on condition in SQL Server

makumbi
 
on Nov 21, 2022 07:02 AM
551 Views
Call bookname author
298.5 INTRODUCATION TO COMPUTER MUKASA
5000 CARTOONS MADE EASY LUBEGA
632.5 INTRODUCATION TO READING  

When the field for the author is empty it should consider the book name and concatenate only 3 letters from the title.

EXPECTED OUT PUT

Call bookname author
298.5MUK INTRODUCATION TO COMPUTER MUKASA
500LUB CARTOONS MADE EASY LUBEGA
298.5INTR INTRODUCATION TO READING  

how can i do this please help.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
arjunv
 
on Nov 21, 2022 07:14 AM
on Nov 21, 2022 07:18 AM

Hi Makumbi,

Use CASE statement with SUBSTRING function.

Please refer below sample query.

SQL

CREATE TABLE #Books
(
    [Call] VARCHAR(50),
    [BookName] VARCHAR(200),
    [Author] VARCHAR(50)
)
INSERT INTO #Books VALUES('298.5', 'INTRODUCATION TO COMPUTER', 'MUKASA')
INSERT INTO #Books VALUES('500', 'CARTOONS MADE EASY', 'LUBEGA')
INSERT INTO #Books VALUES('632.5','INTRODUCATION TO READING','')
 
SELECT CASE WHEN [Author] = '' THEN [Call] + SUBSTRING([BookName],1,3) 
		ELSE [Call] + SUBSTRING([Author],1,3) END AS [Call], [BookName], [Author]
FROM #Books

DROP TABLE #Books

Output 

Call BookName Author
298.5MUK INTRODUCATION TO COMPUTER MUKASA
500LUB CARTOONS MADE EASY LUBEGA
632.5INT INTRODUCATION TO READING