how do i divide a sql variable into 2

Question

hi

i have a field in sql named as address which is of 80 char. i want to put this field into 2 fields addr1 and addr2 of 40 char each. how do i do it.

Answer 1

this is for T-SQL, but it can't be much different for PL/SQL

declare @yourVar varchar(80)
select substring(@yourVar, 1, 40), substring(@yourVar, 40, 40)

Answer 2

for plsql, it's substr(), so select substr(addr, 1, 40) as addr1, substr(addr, 40) as addr2 from ...

Answer 3

I think your schema would be better off if you altered that table to have two columns instead of one. I'd prefer that solution to parsing the current value.

Answer 4

hi we hav to select from wat. i have already selected the addr variable from 1 cursor and need to divide that

Answer 5

Brute-force chopping the 80-character value at position 40 runs the risk of breaking in the middle of a word. You might want to do the following instead:

1. Replace all runs of whitespace with a single blank.
2. Find the last blank at or before position 40.
3. Place everything before that blank in the first result field.
4. Place everything after that blank in the second result field.

The exact details of the operations above will depend on what tools are available to you (e.g. SQL only, or reading from one DB and writing to another using a separate program, etc.)

There is the possibility that the 80-character value may be filled in such a way that breaking between "words" will require one of the result values to be more than 40 characters long to avoid truncation.