SQL Server Select Command - Extract first 2 letters from a column and prefix

I need a SQL Select Command that will:

1. Extract the 1st 2 chars from a column named 'Product_Desc' and then remove spaces.   For example;  1d =  '1 dThis is a test'
2. Prefix the extracted results with 'QB_Index_'.  For example; QB_Index_1d
3. If no value in the 'Product_Desc', then default value to 'QB_OB'

Select Product_Desc from [mytable].  

Sample Data and Expected Result
Product_Desc                                                                NewColumn
 TEE 2 SANITARY SCH 40 PVC UNTHREADE              QB_Index_TE
1 DIA CARBIDE ENDMILL 4F SE CC                             QB_Index_1D
                                                                                         QB_Index_OB
AMY 50W LO VOLT TRANS F/ CAB LGHT                    QB_Index_AM
tmajor99Asked:
Who is Participating?
 
Daniel Van Der WerkenIndependent ConsultantCommented:
Oops, I forgot the empty row. You also want to use a CASE

SELECT
               CASE WHEN LEN(LTRIM(RTRIM(Product_Desc))) > 0 THEN 'QB_Index_' +  SUBSTRING(REPLACE(Product_Desc, ' ', ''), 1, 2)  ELSE 'QB_Index_OB' END AS [NewColumn]
FROM MyTable

Open in new window

0
 
Daniel Van Der WerkenIndependent ConsultantCommented:
You want to use the following SQL Functions:

SUBSTRING()
REPLACE()

SELECT
               'QB_Index_' +  SUBSTRING(REPLACE(Product_Desc, ' ', ''), 1, 2)  AS [NewColumn]
FROM MyTable
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this:
SELECT Product_Desc, 'QB_Index_' + LEFT(REPLACE(ISNULL(Product_Desc,'OB'),' ',''),2) AS QB_Index
FROM ProductDesc

Open in new window

0
 
tmajor99Author Commented:
Very Nice!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.