Counting empty strings parts in MySql

Question

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.

What a curious address format ? Why don't you create a detail table for address with street, zipcode, city, etc. fields ? — Alaindeseine
– Alaindeseine, Commented Jun 8, 2022 at 7:43
i can not change the address format. they are stored like that. — Noel Alex Makumuli
– Noel Alex Makumuli, Commented Jun 8, 2022 at 7:46

Alexey · Accepted Answer · 2022-06-08 08:06:16Z

1

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

answered Jun 8, 2022 at 8:06

Alexey

2,4791 gold badge13 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Counting empty strings parts in MySql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related