Counting empty strings parts in MySql-CodePudding

i need to extract part of a string from a mysql column and check if it is empty, then count the empty parts of the string.

However, i need to check multiple parts of the same string and I am looking for a better way of doing the count.

I have three columns: address1, address2, address3.

in address1:      streetname\postalcode\city\country
in address2: Name3\streetname\postalcode\city\country
in address3: Name3\streetname\postalcode\city\country

The data in the fields comes in the format:

"1","12","streetName\00000\city\SE" "Name12\streetName\00000\city\SE","Name13\streetName\00001\city\SE"
"2","11","streetName\\city\SE","Name11\\00011\city\SE","Name12\\\city\SE"
"3","13","\\\","\\\\","\\\SE"
"4","14","\\\SE","some\\\\SE","address3\\\\SE"
"5","15","street1\\city1\SE","name15\\city2\SE","name15\street3\12345\city3\SE"
"6","16","street1\12345\\SE","name16\street2\\\SE","name16\\\city3\SE"

The columns are in the same same table.

Please kindly advice how to improve the mysql queries below.

SELECT COUNT(LENGTH(TRIM(SUBSTRING_INDEX(address1,'\\',1))) = 0 ) 
        AS "CustomerAddress.StreetName" 
FROM library.table_demo 
WHERE LENGTH (TRIM(SUBSTRING_INDEX(address1,'\\',1))) = 0 ;

SELECT COUNT(LENGTH (TRIM(SUBSTRING_INDEX (SUBSTRING_INDEX(address1,"\\",2) ,"\\",-1)) )=0) 
        AS "CustomerAddress.PostalCode" 
FROM library.table_demo 
WHERE LENGTH (TRIM(SUBSTRING_INDEX (SUBSTRING_INDEX(address1,"\\",2) ,"\\",-1)) )=0 ;

SELECT COUNT(LENGTH( TRIM( SUBSTRING_INDEX (SUBSTRING_INDEX(address1,"\\",3) ,"\\",-1) ) =0) ) 
        AS "CustomerAddress.City"    
FROM library.table_demo 
WHERE LENGTH ( TRIM( SUBSTRING_INDEX (SUBSTRING_INDEX(address1,"\\",3) ,"\\",-1))) = 0 ;

At the moment I am running 6 separate queries to get the result, however, if i can condense these to a single query would be superb.

Thanks for the advice.

CodePudding user response：

As you've posted only 3 queries - one per street name, postal code and city, i give you solution for counting this empty address parts.

WITH address_parts_len(street_name_len, postal_code_len, city_len) AS (
SELECT 
    LENGTH(TRIM(SUBSTRING_INDEX(address1,'\\',1))) as steet_name_len, 
    LENGTH(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(address1,"\\",2) ,"\\",-1))) as postal_code_len,
    LENGTH(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(address1,"\\",3) ,"\\",-1))) as city_len
FROM table_demo
)
SELECT 
    COUNT(IF(street_name_len = 0, 1, NULL)) as empty_street_name_count,
    COUNT(IF(postal_code_len = 0, 1, NULL)) as empty_postal_code_count,
    COUNT(IF(city_len = 0, 1, NULL)) as empty_city_count
FROM address_parts_len

Check a demo https://www.db-fiddle.com/f/oa1ykdZEjxfFATwSNq15wZ/0