Solved

T SQL Select a Constant Value and Concatenate with another Field

Posted on 2016-09-20
13
52 Views
Last Modified: 2016-09-26
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
Comment
Question by:fjkaykr11
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 10

Assisted Solution

by:HuaMinChen
HuaMinChen earned 83 total points
ID: 41807996
Use money if you need comma to the displayed column, like
SELECT CONVERT(varchar, CAST(987654321 AS money), 1)

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 251 total points
ID: 41807998
you do NOT want commas?
 so you want to treat the result as a string....

SELECT '123456' + Customer.CustomerID AS StringID
FROM Customer
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 41808031
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 10

Expert Comment

by:HuaMinChen
ID: 41808035
Try
SELECT replace((VAL("123456")+Customer.CustomerID,',','') FROM Customer

Open in new window

0
 
LVL 50

Assisted Solution

by:Ryan Chong
Ryan Chong earned 83 total points
ID: 41808045
what's the format of Customer.CustomerID ?

what Paul commented in ID: 41807998 should return the result you wanted?
1
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 83 total points
ID: 41808698
Use this:
SELECT '123456'+cast(Customer.CustomerID AS VARCHAR(25)) AS concat_value FROM Customer

Open in new window

0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 41809809
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
 
LVL 3

Author Closing Comment

by:fjkaykr11
ID: 41809813
Thanks
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41810652
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
 
LVL 3

Author Comment

by:fjkaykr11
ID: 41813344
I tried that method, it didn't work.    I am not using SSMS.   Thanks again.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41816021
Can you be a bit more specific? Didn't work is not enough, code sample that you ran with error massage would be.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41816024
Sorry, I just saw that the question had been answered.
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 41817092
no worries, thanks for the assistance.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 31
SQL Availablity Groups Shared Path 2 14
job schedule 8 18
MS SQL + group by time 4 14
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

856 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