T SQL Select a Constant Value and Concatenate with another Field

fjkaykr11
fjkaykr11 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HuaMin ChenProblem resolver
Commented:
Use money if you need comma to the displayed column, like
SELECT CONVERT(varchar, CAST(987654321 AS money), 1)

Open in new window

EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
you do NOT want commas?
 so you want to treat the result as a string....

SELECT '123456' + Customer.CustomerID AS StringID
FROM Customer

Author

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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

HuaMin ChenProblem resolver

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

Open in new window

Ryan ChongSoftware Team Lead
Commented:
what's the format of Customer.CustomerID ?

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

Open in new window

Author

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.

Author

Commented:
Thanks
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)...

Author

Commented:
I tried that method, it didn't work.    I am not using SSMS.   Thanks again.
Can you be a bit more specific? Didn't work is not enough, code sample that you ran with error massage would be.
Sorry, I just saw that the question had been answered.

Author

Commented:
no worries, thanks for the assistance.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial