How to search specific SKU in magento2?

Question

In search bar when i am searching a product using sku (100.40.10),

It displays all the product whose sku is having prefix (100.xx.xx) ,

But i only want it to fetch whose sku is (100.40.10).

Answer 1

By default, while searching for a product through catalog search, Magento uses all attributes for which the value of Use in Search is set to Yes.

If your Magento is not able to search a unique product when your search is being done by SKU, it means there is at least one attribute which is being used for search and the value of the search string is same for some products for that particular attribute.

You may check for the attributes for which the value of Use in Search is set to Yes. Set the value of Use in Search to No for all the attributes except SKU.

After that Reindex and check again. Once the search starts working as expected, you can set the value of Use in Search to Yes for the attributes which you want to include in search.

Answer 2

Magento 1 has feature to set which kind of search you want, but in Magento 2 that feature removed so you need to customize that magento-search module Just create Plugin the customize and change the match strategie Magento\Framework\Search\Adapter\Mysql\Query\Builder\Match and use around Plugin Function technique :

<?php

namespace Test\OneSku\Plugin;

use Magento\Framework\DB\Helper\Mysql\Fulltext;
use Magento\Framework\DB\Select;
use Magento\Framework\Search\Adapter\Mysql\Field\FieldInterface;
use Magento\Framework\Search\Adapter\Mysql\Field\ResolverInterface;
use Magento\Framework\Search\Adapter\Mysql\ScoreBuilder;
use Magento\Framework\Search\Request\Query\BoolExpression;
use Magento\Framework\Search\Request\QueryInterface as RequestQueryInterface;
use Magento\Framework\Search\Adapter\Preprocessor\PreprocessorInterface;
use Magento\CatalogSearch\Model\ResourceModel\Fulltext as FulltextResource;
use Magento\Catalog\Model\ResourceModel\Product\Attribute\Collection as AttributeCollection;
use Magento\Store\Model\StoreManagerInterface;
use Magento\Framework\App\ResourceConnection;
use Test\OneSku\Helper\Data as OneSkuData;

class Match extends \Magento\Framework\Search\Adapter\Mysql\Query\Builder\Match
{
    const SPECIAL_CHARACTERS = '-+~/\\<>\'":*$#@()!,.?`=%&^';
    const MINIMAL_CHARACTER_LENGTH = 3;

    private $replaceSymbols = [];
    private $resolver;
    private $fulltextHelper;
    private $fulltextSearchMode;
    protected $preprocessors;
    private $attributeCollection;
    private $fulltextResource;
    private $storeManager;
    private $_resource;
    protected $dataHelper;

    public function __construct(
        OneSkuData $dataHelper,
        AttributeCollection $attributeCollection,
        FulltextResource $fulltextResource,
        StoreManagerInterface $storeManage,
        ResourceConnection $resource,
        ResolverInterface $resolver,
        Fulltext $fulltextHelper,
        $fulltextSearchMode = Fulltext::FULLTEXT_MODE_BOOLEAN,
        array $preprocessors = []
    ) {
        $this->resolver = $resolver;
        $this->replaceSymbols = str_split(self::SPECIAL_CHARACTERS, 1);
        $this->fulltextHelper = $fulltextHelper;
        $this->fulltextSearchMode = $fulltextSearchMode;
        $this->preprocessors = $preprocessors;
        $this->attributeCollection = $attributeCollection;
        $this->fulltextResource = $fulltextResource;
        $this->storeManager = $storeManage;
        $this->_resource = $resource;
        $this->dataHelper = $dataHelper;
        parent::__construct($resolver,$fulltextHelper, $fulltextSearchMode, $preprocessors);
    }

    public function aroundBuild($matcher, $build, $scoreBuilder, $select, $query, $conditionType)
    {
        $resultSku = 0;
        $queryValue = $this->prepareQuery($query->getValue(), $conditionType);
        $fieldList = [];
        foreach ($query->getMatches() as $match) {
            $fieldList[] = $match['field'];
        }
        $resolvedFieldList = $this->resolver->resolve($fieldList);
        $fieldIds = [];
        $columns = [];
        foreach ($resolvedFieldList as $field) {
            if ($field->getType() === FieldInterface::TYPE_FULLTEXT && $field->getAttributeId()) {
                $fieldIds[] = $field->getAttributeId();
            }
            $column = $field->getColumn();
            $columns[$column] = $column;
        }
        if ($this->dataHelper->allowExtension()) {
            $attribute = $this->attributeCollection->getItemByColumnValue('attribute_code', 'sku');
            $attributeId = $attribute ? $attribute->getId() : 0;
            if ($attributeId != 0) {
                $connection = $this->_resource->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION);
                $mainFullTextScope = $this->fulltextResource->getMainTable() . "_scope" . $this->storeManager->getStore()->getId();
                $tblFullTextScope = $connection->getTableName($mainFullTextScope);
                $resultSku = $connection->fetchOne('SELECT count(entity_id) FROM `' . $tblFullTextScope . '` WHERE attribute_id=' . $attributeId . ' And data_index=\'' . $query->getValue() . '\'');
            }
        }
        if ($resultSku == 1) {
            $matchQuery = 'data_index=\'' . $query->getValue() . '\'';
        } else {
            $matchQuery = $this->fulltextHelper->getMatchQuery(
                $columns, $queryValue, $this->fulltextSearchMode
            );
        }
        $matchQuery = $this->fulltextHelper->getMatchQuery(
                $columns, $queryValue, $this->fulltextSearchMode
        );
        $scoreBuilder->addCondition($matchQuery, true);
        if ($fieldIds) {
            $matchQuery = sprintf('(%s AND search_index.attribute_id IN (%s))', $matchQuery, implode(',', $fieldIds));
        }
        $select->where($matchQuery);
        return $select;
    }
}

In this class we have added some line to test of SKU with some reference is exist by example in table catalogsearch_fulltext_scope1 (related to store id 1)

$attribute = $this->attributeCollection->getItemByColumnValue('attribute_code', 'sku');
$attributeId = $attribute ? $attribute->getId() : 0;
if ($attributeId != 0) {
    $connection = $this->_resource->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION);
    $mainFullTextScope = $this->fulltextResource->getMainTable() . "_scope" . $this->storeManager->getStore()->getId();
    $tblFullTextScope = $connection->getTableName($mainFullTextScope);
    $resultSku = $connection->fetchOne('SELECT count(entity_id) FROM `' . $tblFullTextScope . '` WHERE attribute_id=' . $attributeId . ' And data_index=\'' . $query->getValue() . '\'');
}

If yes we change the clause were to be strict equal without the Boolean match or like.

if ($resultSku == 1) {
    $matchQuery = 'data_index=\'' . $query->getValue() . '\'';
} else {
    $matchQuery = $this->fulltextHelper->getMatchQuery(
        $columns, $queryValue, $this->fulltextSearchMode
    );
}