473,320 Members | 2,145 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

how to form one column values to two column in sql server 2005

hi..
i have one field like as answer. it contains both integer and string values. i want to split into column as ans_id,ans_text.ans_id contain numeric value of answer.
ans_text contain text value of answer.i need help to how to do this?
Oct 22 '07 #1
5 1479
iburyak
1,017 Expert 512MB
You should provide a sample data on what you have and what you want to get.
It is important to know if you have delimiter or not and if Id part is fixed.
Oct 22 '07 #2
You should provide a sample data on what you have and what you want to get.
It is important to know if you have delimiter or not and if Id part is fixed.

i need this format


answer column for ans_id ans_text
2 2 null
3 3 null
Erikje null erikje
Erikje ....................
7
7
2
2
3
3
5
5
1
1
2
2
erik@reiswekker.com
erik@reiswekker.com
3
3
4
4
Erikje 2
Erikje 2
5
5
2
2
2
2
5
5
1
1
4
4
erik@reiswekker.com
erik@reiswekker.com
3
3
2
2
10
10
2
2
6
6
4
4
1
1
3
3
99999997
99999997
1
1
Erik 3
Erik 3
7
7
1
1
47
47
6
6
1
1
3
3
erik@reiswekker.com
erik@reiswekker.com
4
4
4
4
erik
erik
99999997
99999997
2
2
4
4
3
3
2
2
99999998
99999998
erik
erik
4
4
2
2
wefwefwef
wefwefwef
5
5
2
2
4
4
4
4
2
2
99999998
99999998
dfe
dfe
2
2
2
2
5
5
1
1
40
40
5
5
1
1
2
2
4
4
4
4
fdhgdfghdfgh
fdhgdfghdfgh
5
5
2
2
41
41
6
6
2
2
99999998
99999998
gfdfdgh
gfdfdgh
1
1
1
1
5
5
1
1
42
42
6
6
1
1
3
3
1
1
2
2
2
2
1
1
36
36
5
5
1
1
1
1
1
1
2
2
5
5
1
1
51
51
3
3
1
1
4
4
3
3
3
3
999
999
6
6
1
1
56
56
7
7
2
2
99999998
99999998
uuu
uuu
5
5
4
4
5
5
1
1
2
2
6
6
1
1
2
2
1
1
2
2
5
5
1
1
59
59
2
2
2
2
99999998
99999998
4
4
3
3
4
4
1
1
4
4
5
5
2
2
99999998
99999998
4
4
3
3
4
4
2
2
26
26
5
5
2
2
99999998
99999998
erc
erc
4
4
2
2
2
2
1
1
5
5
4
4
2
2
99999998
99999998
4
4
4
4
tyu
tyu
3
3
1
1
22
22
5
5
2
2
99999998
99999998
ytu
ytu
3
3
3
3
gfgf
gfgf
1
1
1
1
4
4
7
7
1
1
1
1
ffg
ffg
3
3
4
4
3
3
2
2
56
56
4
4
2
2
99999998
99999998
j
j
6
6
3
3
5
5
1
1
22
22
3
3
1
1
1
1
3
3
3
3
99999997
99999997
1
1
22
22
5
5
1
1
4
4
5
5
3
3
6
6
2
2
15
15
5
5
2
2
99999998
99999998
2
2
2
2
5
5
1
1
99
99
3
3
1
1
1
1
4
4
4
4
7
7
2
2
46
46
5
5
2
2
99999998
99999998
l;@vv.
l;@vv.
3
3
3
3
r
r
1
1
1
1
60
60
3
3
1
1
2
2
e
e
2
2
2
2
6
6
2
2
16
16
5
5
2
2
99999998
99999998
1
1
1
1
3
3
2
2
1
1
5
5
1
1
5
5
1
1
3
3
5
5
1
1
99
99
3
3
1
1
1
1
1
1
2
2
7
7
1
1
40
40
5
5
2
2
99999998
99999998
1
1
3
3
3
3
1
Oct 22 '07 #3
i culdnt get wat s dis???
i need this format


answer column for ans_id ans_text
2 2 null
3 3 null
Erikje null erikje
Erikje ....................
7
7
2
2
3
3
5
5
1
1
2
2
erik@reiswekker.com
erik@reiswekker.com
3
3
4
4
Erikje 2
Erikje 2
5
5
2
2
2
2
5
5
1
1
4
4
erik@reiswekker.com
erik@reiswekker.com
3
3
2
2
10
10
2
2
6
6
4
4
1
1
3
3
99999997
99999997
1
1
Erik 3
Erik 3
7
7
1
1
47
47
6
6
1
1
3
3
erik@reiswekker.com
erik@reiswekker.com
4
4
4
4
erik
erik
99999997
99999997
2
2
4
4
3
3
2
2
99999998
99999998
erik
erik
4
4
2
2
wefwefwef
wefwefwef
5
5
2
2
4
4
4
4
2
2
99999998
99999998
dfe
dfe
2
2
2
2
5
5
1
1
40
40
5
5
1
1
2
2
4
4
4
4
fdhgdfghdfgh
fdhgdfghdfgh
5
5
2
2
41
41
6
6
2
2
99999998
99999998
gfdfdgh
gfdfdgh
1
1
1
1
5
5
1
1
42
42
6
6
1
1
3
3
1
1
2
2
2
2
1
1
36
36
5
5
1
1
1
1
1
1
2
2
5
5
1
1
51
51
3
3
1
1
4
4
3
3
3
3
999
999
6
6
1
1
56
56
7
7
2
2
99999998
99999998
uuu
uuu
5
5
4
4
5
5
1
1
2
2
6
6
1
1
2
2
1
1
2
2
5
5
1
1
59
59
2
2
2
2
99999998
99999998
4
4
3
3
4
4
1
1
4
4
5
5
2
2
99999998
99999998
4
4
3
3
4
4
2
2
26
26
5
5
2
2
99999998
99999998
erc
erc
4
4
2
2
2
2
1
1
5
5
4
4
2
2
99999998
99999998
4
4
4
4
tyu
tyu
3
3
1
1
22
22
5
5
2
2
99999998
99999998
ytu
ytu
3
3
3
3
gfgf
gfgf
1
1
1
1
4
4
7
7
1
1
1
1
ffg
ffg
3
3
4
4
3
3
2
2
56
56
4
4
2
2
99999998
99999998
j
j
6
6
3
3
5
5
1
1
22
22
3
3
1
1
1
1
3
3
3
3
99999997
99999997
1
1
22
22
5
5
1
1
4
4
5
5
3
3
6
6
2
2
15
15
5
5
2
2
99999998
99999998
2
2
2
2
5
5
1
1
99
99
3
3
1
1
1
1
4
4
4
4
7
7
2
2
46
46
5
5
2
2
99999998
99999998
l;@vv.
l;@vv.
3
3
3
3
r
r
1
1
1
1
60
60
3
3
1
1
2
2
e
e
2
2
2
2
6
6
2
2
16
16
5
5
2
2
99999998
99999998
1
1
1
1
3
3
2
2
1
1
5
5
1
1
5
5
1
1
3
3
5
5
1
1
99
99
3
3
1
1
1
1
1
1
2
2
7
7
1
1
40
40
5
5
2
2
99999998
99999998
1
1
3
3
3
3
1
Oct 22 '07 #4
amitpatel66
2,367 Expert 2GB
i culdnt get wat s dis???
Make use of TRANSLATE function

Eg:

Expand|Select|Wrap|Line Numbers
  1. SELECT TRANSLATE(col_name,'1234567890,',' ') col_character,
  2.              TRANSLATE(UPPER(col_name),'ABCDEFGHIJKLMNOPQRSTUVWXYZ@',' ') col_number
  3. FROM table_name;
  4.  
Oct 22 '07 #5
Make use of TRANSLATE function

Eg:

Expand|Select|Wrap|Line Numbers
  1. SELECT TRANSLATE(col_name,'1234567890,',' ') col_character,
  2.              TRANSLATE(UPPER(col_name),'ABCDEFGHIJKLMNOPQRSTUVWXYZ@',' ') col_number
  3. FROM table_name;
  4.  



thank you..
its not avail in sql server2005.translate avail in oracle.how to do?
Oct 23 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: serge | last post by:
I am reading "SQL Server Query Performance Tuning Distilled", on page 104 it talks about one of the index design recommendations which is to choose the column that has very high selectivity of...
11
by: surya | last post by:
hello sir, i have a table emp ,it has three fields one is empno int ,second is ename varchar(20). and last is salary , emp empno ename salary ----------- ------------...
3
by: sql guy123 | last post by:
This is a real challenge. I hope someone is smart enough to know how to do this. I have a table TABLE1
6
by: jim_geissman | last post by:
Can I create an index on a variation of a column that isn't actually in the table? I have a ParcelNumber column, with values like 123 AB-670 12345ABC 000-00-040 12-345-67 AP34567890
2
by: M Bourgon | last post by:
I have a file I'm trying to do some non-set-based processing with. In order to make sure I keep the order of the results, I want to BULK INSERT into a temp table with an identity column. The spec...
2
by: Mike Baugh | last post by:
I am using visual studio 2005 to develop a form using c# I have 3 datagrids on one form. I can set the row color based on a certain value in a column. However this color applies to all 3...
12
by: parth | last post by:
Hi I want to achieve the following transformation of data using a stored procedure. Source col1 col2(varchar) -------------------------
5
by: SQLMan_25 | last post by:
Hi All, I am trying to create a user defined function that fetches the price of an item. I have written a scalar function that takes itemid and returns its price. Simple version of tables would...
2
by: Chris | last post by:
I have a database column that stores a comma delimited list of foreign keys. Would someone show me how to do a join using the values from a list stored within a record? For example, a record in ...
0
by: jeoffh | last post by:
Background: I am trying to "merge" some attributes into an existing XML column in my MS SQL 2005 database. The general idea is that I have an XML column in a table and I would like to update/delete...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.