Please suggest an SQL query for this scenario

Question

1.00/5 (1 vote)

See more:

"I have a field (D1) as 101, 102, 103 in database table: mastersupport. If have a query - select right(D1,R) from mastersupport, it will give me result - 1, 2, 3. But my requirement is that I want to show the result as
A, B, C instead of 1,2,3".

Please suggest a query to derive the result as A, B, C.

What I have tried:

I tried with the below but got syntax error.

SELECT distinct replace(replace((RIGHT(D1,1)), '1' , ‘A’), '2', ‘B’, ) AS ExtractString from master_support;

Posted 2-Aug-20 10:21am

Mithi Ravi

Updated 4-Aug-20 2:51am

Add a Solution

3 solutions

Solution 2

If the master_support table contains description for id's, you can get these by using query:

SQL

SELECT descr
FROM master_support

If there's no such of field, i'd recommend to add it. See:

SQL

ALTER TABLE master_support
ADD descr VARCHAR(20) NULL;

UPDATE master_support
SET descr = 'A'
WHERE ID = 1;

UPDATE master_support
SET descr = 'B'
WHERE ID = 2;

UPDATE master_support
SET descr = 'C'
WHERE ID = 3;

For further details, please see: Add Columns to a Table (Database Engine) - SQL Server | Microsoft Docs[^]

Posted 2-Aug-20 21:17pm

Maciej Los

Comments

Mithi Ravi 3-Aug-20 11:29am

Thank you! Will test this query.

Mithi Ravi 3-Aug-20 11:52am

Hi, this will alter the main data table. But I need the result to be derived as A, B, C instead of 1,2,3... Any solution please?

Maciej Los 3-Aug-20 16:10pm

If you have updated your table, a query is quite easy: SELECT id, descr from master_support
In other words, you have to list all fields you want to get.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2020-08-02T13:45:00

Solution 1

You can use values to join to your results
e.g

SQL

SELECT m.*,t1.col2
FROM (VALUES (1,'A') , (2,'B') , (3,'C')) 
t1 (col1, col2)
Join master_support m on right(m.D1,1)=t1.col1

Caveat - I've just typed this on my phone, so it's untested and may have ttypos but I hope you get the idea

Posted 2-Aug-20 13:45pm

CHill60

Comments

Mithi Ravi 2-Aug-20 23:30pm

Thank you. Will test this query.

thomaseo · Accepted Answer · 2020-08-04T02:51:00

Solution 3

First solution is nice - alternate

SELECT 
CASE RIGHT(D1,1) 
	WHEN '1' THEN 'A'
	WHEN '2' THEN 'B'
	WHEN '3' THEN 'C'
END AS ExtractString
FROM master_support

Posted 4-Aug-20 2:51am

thomaseo

Comments

CHill60 4-Aug-20 8:59am

That works very well for a small number of substitutions but might get a bit long-winded or difficult to read, if you have to do a lot of them. Good solution though

Mithi Ravi 4-Aug-20 9:13am

Hi, Thank you. In this scenario, there will be 10 substitutions. So I believe this query will work well. Will test and see.

thomaseo 4-Aug-20 9:36am

You may want to consider an ELSE value if not found -

SELECT
CASE RIGHT(D1,1)
WHEN '1' THEN 'A'
WHEN '2' THEN 'B'
WHEN '3' THEN 'C'
ELSE 'ELSEVALUE'
END AS ExtractString
FROM master_support

Mithi Ravi 5-Aug-20 9:57am

Thank you!