1

Topic: Transfer of the character from the cell end in the beginning.

There is column City it is underlined as: ' Moscow '
It is necessary to deduce '  Moscow ', and Moscow is even better '.'
How it can be made?

2

Re: Transfer of the character from the cell end in the beginning.

Shits-data automatically not to clear.
[spoiler] RTFM regexp_replace [/spoiler]

3

Re: Transfer of the character from the cell end in the beginning.

YouIm;
And if there will be "city of Moscow", "Moscow of mountains.", and still cities can write with errors and hundreds different methods. Get the reference manual of cities and refer to it

4

Re: Transfer of the character from the cell end in the beginning.

Select case when table. City = ' Moscow  ' then '  Moscow ' an esche luchshe ' Moscow ' else table.city end as city
from table

5

Re: Transfer of the character from the cell end in the beginning.

YouIm;
The source of arrival of the data can transfer a title and settlement type separately? Or to give the reference on ?

6

Re: Transfer of the character from the cell end in the beginning.

YouIm;

with t as (select ' Moscow  ' as City from dual union
select ' mountains.  ' as City from dual)
Select case when lower (t. City) like ' % % ' then ' Moscow ' else t.city end as city
from t;

7

Re: Transfer of the character from the cell end in the beginning.

-2;
Well it is pure as an analysis example "". On real basis it is possible to look at result of analysis and to correct as it is necessary additional filters. Anyway, here semi-manual analysis, will not be typed yet the necessary rules on all variants.

8

Re: Transfer of the character from the cell end in the beginning.

YouIm, further game of imagination and the real data. If there are gaps in the end if in the end is not present , etc. it is necessary to invent conditions

with t as (
select ' Moscow  ' as city from dual union
select ' Pushkino  ' as city from dual union
select ' PerepElkinO of settlement Zashchelkino ' as city from dual
)
select replace (
substr (city,
instr (city, ' ',-1) +1,
length (city)-instr (city, ' ',-1)
)
,'. ', ") ||
'. ' ||
initcap (
lower (substr (city,
1,
instr (city, ' ',-1)-1
)
)
) as city
from t

9

Re: Transfer of the character from the cell end in the beginning.

YouIm wrote:

As it can be made?

e Address Cleansing/Validation a software. At us it is for example used Trillium. I think there is (not free of charge certainly) similar software and for P addresses (any insurance company and even bank without it does not manage).
SY.

10

Re: Transfer of the character from the cell end in the beginning.

Thanks all for the information, basically in a head that that appeared.

11

Re: Transfer of the character from the cell end in the beginning.

SY wrote:

Sushchestvuet Address Cleansing/Validation a software. At us it is for example used Trillium. I think there is (not free of charge certainly) similar software and for P [/quote addresses
Exists.  is called.
Unfortunately, yet on all regions it is full/is correct.
But free of charge and quite officially smile

12

Re: Transfer of the character from the cell end in the beginning.

andrey_anonymous;
At  is api for  establishments of correspondences?

13

Re: Transfer of the character from the cell end in the beginning.

env wrote:

andrey_anonymous;
At  is api for  establishments of correspondences?

At  there is a possibility to extort to itself basis and to update it.
http://fias.nalog.ru/Updates.aspx
And there though context-sensitive search fasten, though line of the bald.

14

Re: Transfer of the character from the cell end in the beginning.

andrey_anonymous;
I.e. as a matter of fact, to the logician of determination of correspondences to do all the same it is independent
speaks about decisions which already implemented it and guarantee NN % of correspondence of an input to result.

15

Re: Transfer of the character from the cell end in the beginning.

env wrote:

i.e. as a matter of fact, to the logician of determination of correspondences to do all the same it is independent

?
It is not necessary any logic of "determination of correspondences".
It ~ (, ) hierarchically organized basis of addresses to replenish and update which under the law are obliged local administrations, and to which translate (besides the law, and someone - and weighty kicks) all . Structures.
.. Any super-logic here any more it is not required - it is required to pick up closest of addresses available in a DB, it is not especially difficult and (in case of a local copy) does not create risks of inaccessibility of service.
On site  context-sensitive search is quite implemented - it is possible and so, probably, to "be integrated", if laziness own context-sensitive index to fasten.
On  - possibly, probably to arrange referens-visit in tax and to agree about acquisition of their variant of context-sensitive search or still what form of co-operation.

16

Re: Transfer of the character from the cell end in the beginning.

andrey_anonymous;
What is  I represent, both in respect of loading and in respect of operation with this data. But to search there to type addresses "Moscow Lenin 1" - that still for pleasure.

17

Re: Transfer of the character from the cell end in the beginning.

env wrote:

to search there to type addresses "Moscow Lenin 1" - that still for pleasure.

: Try (as recommend) "Lenin Moskva" smile smile smile

18

Re: Transfer of the character from the cell end in the beginning.

env wrote:

But to search there to type addresses "Moscow Lenin 1" - that still for pleasure.

Actually, here it is literally on a knee (aaa_fias_addr_full is only the address objects  quickly loaded   through the idler) blinded a small illustration.
(For real experience of application of context-sensitive indexes in commercial projects, Russian support, etc. it is better to ask the colleague Shtok )
[spoiler]

create table aaa_fias_AO_lines as
with t (aoguid, keys, aline, atyp)
as (select aoguid, formalname, shortname || ' ' || formalname, shortname from aaa_fias_addr_full where aolevel=1 and pareNTguid is null and livestatus = 1
union all
select c.aoguid, t.keys || ' ' || c.formalname
, t.aline || ', ' || c.shortname || ' ' || c.formalname
, c.shortname
from t, aaa_fias_addr_full c where t.aoguid = c.pareNTguid and c.livestatus = 1
)
select *
from t
;
Table created
select count (*) from aaa_fias_AO_lines;
COUNT (*)
----------
1282495
create index aaa_fias_AO_lines$DR on aaa_fias_AO_lines (keys) INDEXTYPE IS ctxsys.context;
Index created
set timing on
select aoguid, atyp, aline from aaa_fias_AO_lines
where contains (keys, ' Lenin AND Moscow ', 1)> 0
order by score (1) desc
;
AOGUID ATYP ALINE
------------------------------------ ---------- --------------------------------------------------------------------------------
0faa0cfa-dfe8-4f5a-a001-2727a41d7f21   Moscow,  Zelenograd,  Lenin
4350977e-e152-4018-a33d-b13143bbecda   Moscow,  Tolstopaltsevo,  Lenin
7a8015ad-a3eb-4fd0-b4c4-c34ef55bb294   Moscow,  Vnukovsky,  Michurinets,  Lenin
fdbcc75b-1ce8-4eec-b790-33a268df37ab   Moscow,  Kokoshkino,  Kokoshkino,  Lenin
427d5fbf-f53c-43ee-a768-48a5cd49caf5   Moscow,  Vnukovsky,  Minvneshtorga,  Lenin
f168f4da-a379-46df-b4ce-0cf421ad7475   Moscow,  Krasnopahorsky, with Red Pahra,  Lenin
2417419d-a891-41b5-a9c4-7018283d01e3   Moscow,  Vnukovsky,   Michurinets,  Lenin
7 rows selected
Executed in 0,057 seconds
select aoguid, atyp, aline
from aaa_fias_AO_lines
where contains (keys, ' Moscow AND Lenin of % ', 1)> 0
order by score (1) desc
;
AOGUID ATYP ALINE
------------------------------------ ---------- --------------------------------------------------------------------------------
b05ce965-3bb0-4495-87f4-5eed877d3382   Moscow,  Young Lenintsev
fdbcc75b-1ce8-4eec-b790-33a268df37ab   Moscow,  Kokoshkino,  Kokoshkino,  Lenin
91ab1cb1-d0a4-489c-9182-9dfbabcd9e2b   Moscow,  Leninogorsky
9c3e9392-0324-4d21-9cf5-70076f1b5e15 pr-kt  Moscow, pr-kt Leningrad
c5fec714-04f5-4cb3-89d8-d7cec97116e6   Moscow,  Leningrad
62d9bfcb-278d-48e6-ba60-c23e9776d305   Moscow,  the Lenin Large village
5f2a1243-a57b-418e-baee-ff76f4993b45 pr-kt  Moscow, pr-kt Lenin
12bf2472-62ee-4db6-b998-565141e7a9d1   Moscow,  Lenin Mountains
0faa0cfa-dfe8-4f5a-a001-2727a41d7f21   Moscow,  Zelenograd,  Lenin
051ce76a-0a0f-4234-a11b-dfa9b85bf6f8   Moscow,  Voskresensky,  the Lenin Banner
e703d5fd-4655-48c9-8768-51a3818ebab1 Rubbed  Moscow,  Voskresensky, rubbed  the Lenin Banner
4350977e-e152-4018-a33d-b13143bbecda   Moscow,  Tolstopaltsevo,  Lenin
6880778d-fab1-4a86-998e-40d0e2fdffcb rubbed  Leningrad, - Tosnensky,  Uljanovka, rubbed 656 / roads Moscow-joint ventures
7a8015ad-a3eb-4fd0-b4c4-c34ef55bb294   Moscow,  Vnukovsky,  Michurinets,  Lenin
2417419d-a891-41b5-a9c4-7018283d01e3   Moscow,  Vnukovsky,   Michurinets,  Lenin
427d5fbf-f53c-43ee-a768-48a5cd49caf5   Moscow,  Vnukovsky,  Minvneshtorga,  Lenin
f168f4da-a379-46df-b4ce-0cf421ad7475   Moscow,  Krasnopahorsky, with Red Pahra,  Lenin
87674309-4a67-4fc3-9071-b9c6d70993b7 rubbed  Moscow, rubbed Lenin Mountains
18 rows selected
Executed in 0,274 seconds
SQL>

[/spoiler]

19

Re: Transfer of the character from the cell end in the beginning.

YouIm wrote:

There is column City it is underlined as: ' Moscow '
It is necessary to deduce '  Moscow ', and Moscow is even better '.'
How it can be made?

It is possible to make all. But here it is necessary to beat at first those who invented the form that allows to enter
Cities without selecting them from the reference manual, then to beat the architect, it is desirable feet that allowed
Such in  let out. About to beat the joke, but I would state to such lovely people much.
Most likely it is any downloaded data from fetid pools of ex-fir groves and Words.
To try to forbid such once and for all, delivering a guillotine which is chopping off a head by that who will be
Still to try to push the such.
Or to sit and dig, trying to automate a brothel. As a result receiving the automated brothel.

Posts [ 19 ]

Pages 1

You must login or register to post a reply