Get WooCommerce products list that have similar SKU

Question

How can I get an object list of products with SKU that starts with the same value? Example:

I've following products:

• Article 1 - Sku: ART_1
• Article 2 - Sku: ART_2
• Article 3 - Sku: ART_3

I want to get all products that starts with "ART_". Thanks in advance.

Answer 1

The following custom function, will query all product Id(s) and sku(s) (standard objects) using a light SQL query from a sku excerpt (as ART_):

wc_get_products_ids_and_skus( $sku_excerpt ) {
    global $wpdb;

    // Get all product Ids and skus (standard objects) from a sku excerpt
    return $wpdb->get_results( $wpdb->prepare( "
        SELECT p.ID as id, pm.meta_value as sku
        FROM {$wpdb->prefix}posts p
        INNER JOIN {$wpdb->prefix}postmeta pm
            ON p.ID = pm.post_id
        WHERE p.post_type = 'product'
        AND p.post_status = 'publish'
        AND pm.meta_key = '_sku'
        AND pm.meta_value LIKE '%s'
    ", '%'.$sku_excerpt.'%' ) );
}

Code goes in functions.php file of your active child theme (or active theme). Tested and works.

USAGE (example):

$sku_excerpt = 'ART_';

$results = wc_get_products_ids_and_skus( $sku_excerpt );

// Loop through results
foreach ( $results as $result ) {
    $product_id  = $result->id;
    $product_sku = $result->sku;

    // Get the WC_Product Object (if needed)
    $product     = wc_get_product( $product_id );

    // Testing output
    echo 'Product Id '.$product_id.' and sku '.$product_sku.'<br>'; 
}