Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Columns

Posted on 2010-01-09
8
Medium Priority
?
212 Views
Last Modified: 2012-05-08
I want to convert this back to one column by concatenating the variables i.e.   (@a1 " & " @a2) can this be done?


DECLARE @A1 VARCHAR(2),
            @A2 VARCHAR(2),
            @A3 VARCHAR(2),
            @A4 VARCHAR(2),
            @A5 VARCHAR(2),
            @A6 VARCHAR(2)



SELECT SUBSTRING (LottoNum,1, 2) AS A1, SUBSTRING (LottoNum,3, 2) AS A2,SUBSTRING (LottoNum,5, 2) as A3,SUBSTRING (LottoNum,7, 2) AS A4,
SUBSTRING (LottoNum,9, 2) AS A5, SUBSTRING (LottoNum,11, 2) AS A6
     
FROM [Lotto].[dbo].[LottoAccount]
0
Comment
Question by:Dell12805
  • 4
  • 2
  • 2
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26274909
your code does not fill @a1 etc ...

SELECT @a1 = SUBSTRING (LottoNum,1, 2) AS A1,  etc ...

as from then, you can do :
set @result = @a1 + @a2 ... etc ..
0
 
LVL 59

Expert Comment

by:Bill Prew
ID: 26275015
In this particular example you could of course do this as well.

SELECT @result = SUBSTRING(LottoNum,1, 12) FROM [Lotto].[dbo].[LottoAccount]

That may not work if you to take the data from the vars rather than the table, but since the pieces were contiguous in the original field this could be an alternate approach.

~bp
0
 

Author Comment

by:Dell12805
ID: 26275082
This did not work.

DECLARE @A1 VARCHAR(2),
        @A2 VARCHAR(2),
            @result VARCHAR(20)

SET @result = @A1 + @A2

SELECT @result = (SUBSTRING (LottoNum,1, 2), SUBSTRING (LottoNum,3, 2))
from dbo.LottoAccount

print @result
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Dell12805
ID: 26275104
I tried this as well Bill

DECLARE @A1 VARCHAR(2),
        @A2 VARCHAR(2),
            @result VARCHAR(20)


SELECT @result = SUBSTRING (LottoNum,1, 2) AS A1, SUBSTRING (LottoNum,3, 2) AS A2
SET @result = @A1 + @A2

from dbo.LottoAccount

print @result
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26275121
your code does not make much sense so far.
you set @restult, but overwrite it then again ...
0
 
LVL 59

Accepted Solution

by:
Bill Prew earned 2000 total points
ID: 26275134
Either of these should work:

DECLARE @A1 VARCHAR(2),
        @A2 VARCHAR(2),
        @result VARCHAR(20)
SELECT @a1 = SUBSTRING (LottoNum,1, 2) FROM [Lotto].[dbo].[LottoAccount]
SELECT @a2 = SUBSTRING (LottoNum,3, 2) FROM [Lotto].[dbo].[LottoAccount]
set @result = @a1 + @a2
print @result

DECLARE @result VARCHAR(20)
SELECT @result = SUBSTRING(LottoNum,1, 2) + SUBSTRING(LottoNum,3, 2) FROM [Lotto].[dbo].[LottoAccount]
print @result

~bp
0
 

Author Comment

by:Dell12805
ID: 26275136
This is why I am asking for help :


SELECT @result = SUBSTRING (LottoNum,1, 2) AS A1, SUBSTRING (LottoNum,3, 2) AS A2


from dbo.LottoAccount

print @result
0
 

Author Closing Comment

by:Dell12805
ID: 31675040
Thanks for your help
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
Without even knowing it, most of us are using web applications on a daily basis.  In fact, Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We generally confuse these web applications to…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
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…
Suggested Courses

580 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