August 23, 2011 at 1:51 am
hi guys
this is my first post. hope you can help.
im very new to SQL and Access so forgive me. but...
i have a column of around 1000 rows, heres an example of what it looks like;
GBP2000.00
AUD190.00
EUR5.00
and the list goes on. what i need to do is split each of these into 2 columns. column A with the first 3 letters, and column B with the remaining numbers. can this be done?
again i am very new to this so do not be afraid to insult my intelligence by dumbing your answer down! thanks in advance.
August 23, 2011 at 2:18 am
You can use the substring function to do that. You can find more details about this function in BOL (Books On Line).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 23, 2011 at 9:40 am
Since you posted this in the Access forum, you can use the Left() function to select the first three characters of the string, and the Mid() function to select the remaining characters starting with character 4. See the VBA help in Access for full definition of the functions, or do a web search on the two different functions. Also note that those functions can be used in Access queries to create calculated values.
Wendell
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
August 23, 2011 at 11:20 pm
It is much easy in Excel...
Open Excel and Add value in A1 cell -> GBP500
Write in B1
=LEFT(A1,3)
Write in C1
=RIGHT(A1,LEN(A1)-3)
After that you can add Excel data into your Table...with 2 column
August 24, 2011 at 12:51 am
Adi Cohn-120898 (8/23/2011)
You can use the substring function to do that. You can find more details about this function in BOL (Books On Line).Adi
Sorry, didn't notice that it was posted on Access forum. My answer is wrong because I was thinking of SQL Server
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 24, 2011 at 2:45 am
thanks guys. ive figured it out.
SELECT LEFT(all6,3) AS curr, COUNT(*) AS volume, SUM(MID (all6, 4, 20)) AS figure
FROM all6
GROUP BY LEFT(all6,3);
gives me the total volumes and balances of each currency type.
cheers for your help. much appreciated.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply