Solved

Concatenate String in SQL

Posted on 2014-03-26
4
210 Views
Last Modified: 2014-05-13
In my code, I'm trying to get get the result to be 0001.DESCRIPTION
Problem is I can't figure out how to properly concatenate.
Can  someone help.

Thankss

I currently have the following code:

SELECT RIGHT('0000' + CAST((glm.COMPANY) AS VARCHAR(4)),4) AS COMPANY,
       
       glm.ACCT_UNIT,
       glm.[DESCRIPTION],
      (CASE
        WHEN glm.ACCT_UNIT = '15200' THEN (COMPANY) + glm.[DESCRIPTION])
        --+glm.[DESCRIPTION])
        ELSE NULL
      END) "Report Group"
FROM LSPROD.dbo.GLNAMES glm  
WHERE glm.ACCT_UNIT in ('15200') and COMPANY = 5
0
Comment
Question by:metalteck
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 39957414
SELECT '0001.'+CAST(glm.COMPANY AS VARCHAR(4)) AS COMPANY,
       glm.ACCT_UNIT,
       glm.[DESCRIPTION],
      CASE
        WHEN glm.ACCT_UNIT = '15200' THEN COMPANY + glm.[DESCRIPTION]
        --+glm.[DESCRIPTION])
        ELSE NULL
      END as [Report Group]
FROM LSPROD.dbo.GLNAMES glm  
WHERE glm.ACCT_UNIT in ('15200') and COMPANY = 5
0
 

Author Comment

by:metalteck
ID: 39957428
I'm sorry, the company can by dynamic, thats why I use Right('0000'....
Regardless, when I tried your code, I got an error that says :
Conversion failed when converting the varchar value 'CPM (Company 5) 15200         ' to data type smallint.

CPM (Company 5) 15200 is the company name. So to revise my previous example, the result I would like to see is 0005.CPM (Company 5) 15200

Thanks
0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39957479
Assumes you have less than 10,000 companies:
SELECT	RIGHT('0000' + CAST(COMPANY as varchar), 4) + '.' + [DESCRIPTION]
FROM	LSPROD.dbo.GLNAMES
WHERE	ACCT_UNIT = '15200'
AND	COMPANY = 5

Open in new window

0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39957590
SELECT ca1.COMPANY_4 AS COMPANY,
       glm.ACCT_UNIT,
       glm.[DESCRIPTION],
       ca1.COMPANY_4 + '.' + glm.[DESCRIPTION] AS [Report Group]
FROM LSPROD.dbo.GLNAMES glm  
CROSS APPLY (
    SELECT RIGHT('0000' + CAST((glm.COMPANY) AS VARCHAR(4)),4) AS COMPANY_4
) AS ca1
WHERE
    glm.ACCT_UNIT in ('15200') and
    COMPANY = 5
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question