Clever Geek Handbook

How to split sql variable by 2

I have a field in sql whose name is 80 char. I want to put this field in 2 fields addr1 and addr2 of 40 char each. how i do it.

0


source to share


4 answers


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



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


source


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



+2


source


I think your schema would be better if you change this table with two columns instead of one. I would prefer this solution to parse the current value.

0


source


Brute force grinding an 80-digit value at position 40 risks breaking in the middle of a word. Instead, you can do the following:

  • Replace all spaces with spaces with one space.
  • Find the last space at position 40 or below.
  • Put everything up to that space in the first result box.
  • Put everything after that space in the second field of the result.

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

There is a possibility that an 80-digit value could be filled in such a way that a gap between words would cause one of the result values ​​to be more than 40 characters long to avoid truncation.

0


source






More articles:


All Articles