• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 70
  • Last Modified:

T SQL Select a Constant Value and Concatenate with another Field

I am using a proprietary front end application to connect to a SQL Server database.
I want to pad a field with a constant string, by using the code below it works with one minor issue * see below:

SELECT (VAL("123456")+Customer.CustomerID FROM Customer

The return value inserts commas every third character:
123,456,555,555,555,555

I tried a few different changes to the syntax but still getting the same results.   The application I use to connect to the SQL Server Database isn't easy to work with - so it is quite a challenge.  Thanks for any info.
0
fjkaykr11
Asked:
fjkaykr11
  • 5
  • 4
  • 2
  • +2
4 Solutions
 
HuaMinChenBusiness AnalystCommented:
Use money if you need comma to the displayed column, like
SELECT CONVERT(varchar, CAST(987654321 AS money), 1)

Open in new window

0
 
PortletPaulCommented:
you do NOT want commas?
 so you want to treat the result as a string....

SELECT '123456' + Customer.CustomerID AS StringID
FROM Customer
0
 
fjkaykr11Author Commented:
Paul thanks for the reply, sorry for the confusion.  That is correct I do not want commas in the return value.
I would like it to be displayed as 123456555555555  instead of 123,456,555,555,555
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
HuaMinChenBusiness AnalystCommented:
Try
SELECT replace((VAL("123456")+Customer.CustomerID,',','') FROM Customer

Open in new window

0
 
Ryan ChongCommented:
what's the format of Customer.CustomerID ?

what Paul commented in ID: 41807998 should return the result you wanted?
1
 
ZberteocCommented:
Use this:
SELECT '123456'+cast(Customer.CustomerID AS VARCHAR(25)) AS concat_value FROM Customer

Open in new window

0
 
fjkaykr11Author Commented:
thanks for all the replies. I might not be able to do this as many functions do not work
in the app I am using (get a lot of errors), including with CAST.
when I tried something like this:
SELECT '123456'+convert(text,18,Customer.CustomerID) AS concat_value FROM Customer
it will bring return a value without errors but still getting the same results (commas after every 3rd character), String did the same.  Using VarChar brings back less values and still has the commas.  I think I will close this out.  Thanks again.
0
 
fjkaykr11Author Commented:
Thanks
0
 
ZberteocCommented:
Why using CAST to text? Nobody posted that. text as data type is obsolete in SQL server you what I posted, CAST to VARCHAR(size)...
0
 
fjkaykr11Author Commented:
I tried that method, it didn't work.    I am not using SSMS.   Thanks again.
0
 
ZberteocCommented:
Can you be a bit more specific? Didn't work is not enough, code sample that you ran with error massage would be.
0
 
ZberteocCommented:
Sorry, I just saw that the question had been answered.
0
 
fjkaykr11Author Commented:
no worries, thanks for the assistance.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now