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:

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
Use money if you need comma to the displayed column, like
SELECT CONVERT(varchar, CAST(987654321 AS money), 1)

Open in new window

PortletPaulEE Topic AdvisorCommented:
you do NOT want commas?
 so you want to treat the result as a string....

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

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

HuaMin ChenProblem resolverCommented:
SELECT replace((VAL("123456")+Customer.CustomerID,',','') FROM Customer

Open in new window

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

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.
fjkaykr11Author Commented:
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)...
fjkaykr11Author 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.
fjkaykr11Author Commented:
no worries, thanks for the assistance.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.