?
Solved

select statement with concatenation for three fields

Posted on 2011-05-09
9
Medium Priority
?
222 Views
Last Modified: 2012-06-27
Hi all,

Is there a way to select in vb.net kind of like the following:

select ID, fname, Year & Day & month from where foo = 5

I have this in a for loop

can it be done?
0
Comment
Question by:tjpal
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 25

Expert Comment

by:Ron Malmstead
ID: 35724647
SELECT  ID, fname,TheDate=[Year] + [Day] + [Month]
WHERE Foo='5'
0
 
LVL 25

Expert Comment

by:Ron Malmstead
ID: 35724659
SELECT  ID, fname,TheDate=[Year] + [Day] + [Month]
FROM YourTableName
WHERE Foo='5'
0
 
LVL 25

Expert Comment

by:Ron Malmstead
ID: 35724665
What exactly are you trying to do ?

...if you post some code and explain a little what you are trying to accomplish I can probably help you better.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:tjpal
ID: 35724709
I tried that, and thought it would work:

I tried:

SELECT ID, SR_ID, SR_Name, SR_Name_Show, SR_Address, SR_City, SR_State, SR_zip, SR_Phone, SR_Phone_Show, " & _
        " SR_Fax, SR_Fax_Show, SR_Email, SR_JobID, SR_OpeningsID, SR_JobTitle, JobTypeCode, SR_SalaryRangeBase, SR_SalaryRangeHigh, " & _
        " SR_LocName, SR_City1, SR_State1, SR_Zip1, SR_Date, SR_Job_Status,
JobBody=[SR_MCA_Other_Text] + [PositionSummary] + [SR_PosReqs] from TheTable

close though
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35724719
Before concatinating numerical values, you need to convert them to character values.
select ID, fname, convert(varchar,[Year]) + convert(varchar,[Day])+convert(varchar,[month]) from table1 where foo = 5

Open in new window

As asked by xuserx2000, post your code. If your column is datetime/date datatype, you need not to extract year, month,day individually to get your required result.
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35724905
try
SELECT ID, SR_ID, SR_Name, SR_Name_Show, SR_Address, SR_City, SR_State, SR_zip, SR_Phone, SR_Phone_Show, " & _
        " SR_Fax, SR_Fax_Show, SR_Email, SR_JobID, SR_OpeningsID, SR_JobTitle, JobTypeCode, SR_SalaryRangeBase, SR_SalaryRangeHigh, " & _
        " SR_LocName, SR_City1, SR_State1, SR_Zip1, SR_Date, SR_Job_Status, " & _
        "[SR_MCA_Other_Text] + [PositionSummary] + [SR_PosReqs] JobBody " & _
from TheTable

Open in new window

0
 

Author Comment

by:tjpal
ID: 35724993


I figured some more of it.  Here is what worked:

Cast(SR_MCA_Other_Text AS VARCHAR(5000)) +''+  CAST(PositionSummary AS VARCHAR(5000)) +''+ CAST(SR_PosReqs AS VARCHAR(5000)) AS JOBBODY
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 2000 total points
ID: 35725020

Yes, you have to cast to varchar if the fields themselves are not varchar fields
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35728039
select ID, fname, Year + ' ' + Day + ' ' + month from where foo = 5
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

579 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