sql语句汇集

1、p表里的SELECTION_ZJ_DATE，时间=END_DATE+2天不

UPDATE t_zj_purchase set SELECTION_ZJ_DATE =FROM_UNIXTIME(UNIX_TIMESTAMP(END_DATE) + 172800) 

2、T_ZJ_PURCHASE表END_DATE = t_zj_purchase_notice表CREATE_DATE +864000

UPDATE T_ZJ_PURCHASE zp SET zp.END_DATE = FROM_UNIXTIME(UNIX_TIMESTAMP((select zpn.CREATE_DATE FROM t_zj_purchase_notice zpn WHERE zpn.PURCHASE_OID = zp.OID) ) + 432000)

3、函数替换，将字符串里四位的字符串替换掉

DELIMITER //

CREATE FUNCTION FIND_FOUR_DIGIT_STRING(input_string VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
    DECLARE result_string VARCHAR(255) DEFAULT '';
    
    SET @input_length = LENGTH(input_string);
    SET @start_pos = 1;
    SET @end_pos = 1;
    
    WHILE @end_pos <= @input_length DO
        SET @end_pos = LOCATE(',', input_string, @start_pos);
        
        IF @end_pos = 0 THEN
            SET @end_pos = @input_length + 1;
        END IF;
        
        SET @value = SUBSTRING(input_string, @start_pos, @end_pos - @start_pos);
        
        IF @value REGEXP '^[0-9]{4}$' THEN
            SET result_string = CONCAT(result_string, ',', @value);
        END IF;
        
        SET @start_pos = @end_pos + 1;
    END WHILE;
    
    RETURN IF(LENGTH(result_string) > 0, SUBSTRING(result_string, 2), NULL);
END //

DELIMITER ;

4、函数替换，将字符串里七位的字符串替换掉

DELIMITER //

CREATE FUNCTION FIND_SEVEN_DIGIT_STRING(input_string VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
    DECLARE result_string VARCHAR(255) DEFAULT '';
    
    SET @input_length = LENGTH(input_string);
    SET @start_pos = 1;
    SET @end_pos = 1;
    
    WHILE @end_pos <= @input_length DO
        SET @end_pos = LOCATE(',', input_string, @start_pos);
        
        IF @end_pos = 0 THEN
            SET @end_pos = @input_length + 1;
        END IF;
        
        SET @value = SUBSTRING(input_string, @start_pos, @end_pos - @start_pos);
        
        IF @value REGEXP '^[0-9]{7}$' THEN
            SET result_string = CONCAT(result_string, ',', @value);
        END IF;
        
        SET @start_pos = @end_pos + 1;
    END WHILE;
    
    RETURN IF(LENGTH(result_string) > 0, SUBSTRING(result_string, 2), NULL);
END //

DELIMITER ;