/* 01.12.2012 3 Отпадащи НЗОК кодове от следващ период */ create or alter procedure SP_PARSE_NAMES ( ANAMES varchar(100)) returns ( FIRST_NAME varchar(100), SUR_NAME varchar(100), FAMILY_NAME varchar(100)) as declare variable ANAME_INDEX integer; declare variable AINDEX integer; declare variable ALENGTH integer; declare variable ACURRENT_CHAR char(1); declare variable APREV_CHAR char(1); BEGIN ANAME_INDEX = 0; FIRST_NAME = ''; SUR_NAME = ''; FAMILY_NAME = ''; AINDEX = 1; ALENGTH = 0; SELECT SP.LEN FROM SP_GET_LEN(:ANAMES) SP INTO :ALENGTH; APREV_CHAR = ''; ACURRENT_CHAR = ''; WHILE (AINDEX <= ALENGTH) DO BEGIN --EXECUTE STATEMENT --'SELECT CAST(SUBSTRING(RES FROM ' || AINDEX || ' FOR 1) AS CHAR(1)) FROM SP_REPEAT(''' || ANAMES || ''')' --INTO -- :ACURRENT_CHAR; SELECT CAST(SP.RESULT AS CHAR(1)) FROM SP_SUBSTRING(:ANAMES, :AINDEX, 1) SP INTO :ACURRENT_CHAR; IF (ACURRENT_CHAR <> ' ') THEN BEGIN APREV_CHAR = ACURRENT_CHAR; IF (ANAME_INDEX = 0) THEN FIRST_NAME = FIRST_NAME || ACURRENT_CHAR; ELSE IF (ANAME_INDEX = 1) THEN SUR_NAME = SUR_NAME || ACURRENT_CHAR; ELSE IF (ANAME_INDEX = 2) THEN FAMILY_NAME = FAMILY_NAME || ACURRENT_CHAR; END ELSE IF ((ACURRENT_CHAR = ' ') AND (APREV_CHAR <> ' ')) THEN BEGIN ANAME_INDEX = ANAME_INDEX + 1; IF (ANAME_INDEX > 2) THEN ANAME_INDEX = 2; APREV_CHAR = ACURRENT_CHAR; IF (ANAME_INDEX = 0) THEN FIRST_NAME = FIRST_NAME || ACURRENT_CHAR; ELSE IF (ANAME_INDEX = 1) THEN SUR_NAME = SUR_NAME || ACURRENT_CHAR; ELSE IF (ANAME_INDEX = 2) THEN FAMILY_NAME = FAMILY_NAME || ACURRENT_CHAR; END AINDEX = AINDEX + 1; END IF ((FAMILY_NAME = '') AND (SUR_NAME != '')) THEN BEGIN FAMILY_NAME = SUR_NAME; SUR_NAME = ''; END SUSPEND; END