Query That Ignore the Spaces

Query that ignore the spaces

SELECT * FROM mytable 
    WHERE REPLACE(username, ' ', '') = REPLACE("John Bob Jones", ' ', '')

SQL query to ignore space

You can use RTRIM() to remove spaces from the right and LTRIM() to remove spaces from the left hence left and right spaces removed as follows:

SELECT * FROM mytable 
    WHERE LTRIM(RTRIM(username)) = LTRIM(RTRIM("John Bob Jones"))

How can I ignore spaces in mysql search query with LIKE?

You are putting the replace on the wrong side. Just to be safe, let's do it for both operands:

REPLACE(course_code, ' ', '') LIKE REPLACE(:searchText, ' ', '')

Active Record Query 'where' to ignore spaces

You can search by name without spaces. Something like this (for postgres):

Model.where("replace(name, ' ', '') = replace(?, ' ', '')", 'Hello  beau tifulworld !')

Doctrine query - ignoring spaces

Ok I write a replace DQL Function.

<?php

namespace Acme\UserBundle\DQL;

use Doctrine\ORM\Query\Lexer; 
use Doctrine\ORM\Query\AST\Functions\FunctionNode; 


/**
 * "REPLACE" "(" StringPrimary "," StringSecondary "," StringThird ")"
 */
class replaceFunction extends FunctionNode{

    public $stringFirst; 
    public $stringSecond; 
    public $stringThird; 


    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker) {
        return  'replace('.$this->stringFirst->dispatch($sqlWalker) .','
                . $this->stringSecond->dispatch($sqlWalker) . ',' 
                .$this->stringThird->dispatch($sqlWalker) . ')';
    }

    public function parse(\Doctrine\ORM\Query\Parser $parser) {

        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->stringFirst = $parser->StringPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->stringSecond = $parser->StringPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->stringThird = $parser->StringPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

}

Next in app/config.yml I add:

doctrine:
    orm:
        auto_generate_proxy_classes: "%kernel.debug%"
        auto_mapping: true
        dql:
            string_functions:
                replace: Acme\UserBundle\DQL\replaceFunction

And finally I create a DQL query in my Controller:

$em = $this->getDoctrine()->getManager();

    $query = $em->createQueryBuilder();

    $query->select('u')
            ->from('Acme\UserBundle\Entity\User', 'u')

            ->where("replace(u.username,' ','') LIKE replace(:username,' ','') ")
            ->setParameter('username', '%' . $usernameForm . '%')
            ->orderBy('u.username', 'asc');


    $result = $query->getQuery()->getResult();

The most funny thing is that "quotes" are very important. It means that you can see that in select, from, setParameter and orderBy I use '' but in where I use "" and space ''. The opposite is not working. I don`t know why.

Efficient way to ignore whitespace in DB2?

Trailing spaces are ignored in Db2 for comparison purpose, so you only need to consider the leading and embedded spaces.

Assuming there is an index on the Identifier, your only option (if you can't change the data, or add a functional index or index a generated column), is probably something like this

SELECT * FROM T
WHERE
    Identifier = 'ID1 ID2'
OR  Identifier = ' ID1 ID2'
OR  Identifier = '  ID1 ID2'
OR  Identifier = 'ID1  ID2'
OR  Identifier = ' ID1  ID2'
OR  Identifier = '  ID1  ID2'

which the Db2 optimize might implement as 6 index lookups, which would be faster than a full index or table scan

You could also try this

SELECT * FROM T
WHERE
    Identifier LIKE 'ID1 %ID2'
OR  Identifier LIKE ' ID1 %ID2'
OR  Identifier LIKE '  ID1 %ID2'

which the Db2 optimize might implement as 3 index range scans,

In both examples add more lines to cover the maximum number of leading spaces you have in your data if needed. In the first example add more lines for the embeded spaces too if needed

Ignore spaces in Elasticsearch

You need a shingle token filter. Simple example.

1. create index with settings

PUT joinword
{
    "settings": {
        "analysis": {
            "filter": {
                "word_joiner": {
                    "type": "shingle",
                    "output_unigrams": "true",
                    "token_separator": ""
                }
            },
            "analyzer": {
                "word_join_analyzer": {
                    "type": "custom",
                    "tokenizer": "standard",
                    "filter": [
                        "lowercase",
                        "word_joiner"
                    ]
                }
            }
        }
    }
}

2. check that analyzer work as expected

GET joinword/_analyze?pretty
{
  "analyzer": "word_join_analyzer",
  "text": "ONE TWO"
}

output:

{
  "tokens" : [ {
    "token" : "one",
    "start_offset" : 0,
    "end_offset" : 3,
    "type" : "<ALPHANUM>",
    "position" : 0
  }, {
    "token" : "onetwo",
    "start_offset" : 0,
    "end_offset" : 7,
    "type" : "shingle",
    "position" : 0
  }, {
    "token" : "two",
    "start_offset" : 4,
    "end_offset" : 7,
    "type" : "<ALPHANUM>",
    "position" : 1
  } ]
}

So now you can find this document by one, two or onetwo. A search will be case insensitive.

Working Spring example

Full project available on GitHub.

Entity:

@Document(indexName = "document", type = "document", createIndex = false)
@Setting(settingPath = "elasticsearch/document_index_settings.json")
public class DocumentES {
    @Id()
    private String id;
    @Field(type = String, analyzer = "word_join_analyzer")
    private String title;

    public DocumentES() {
    }

    public DocumentES(java.lang.String title) {
        this.title = title;
    }

    public java.lang.String getId() {
        return id;
    }

    public void setId(java.lang.String id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    @Override
    public java.lang.String toString() {
        return "DocumentES{" +
                "id='" + id + '\'' +
                ", title='" + title + '\'' +
                '}';
    }
}

Main:

@SpringBootApplication
@EnableConfigurationProperties(value = {ElasticsearchProperties.class})
public class Application implements CommandLineRunner {
    @Autowired
    ElasticsearchTemplate elasticsearchTemplate;

    public static void main(String[] args) {
        SpringApplication.run(Application.class);
    }

    @Override
    public void run(String... args) throws Exception {
        elasticsearchTemplate.createIndex(DocumentES.class);
        elasticsearchTemplate.putMapping(DocumentES.class);

        elasticsearchTemplate.index(new IndexQueryBuilder()
                .withIndexName("document")
                .withType("document")
                .withObject(new DocumentES("ONE TWO")).build()
        );

        Thread.sleep(2000);
        NativeSearchQuery query = new NativeSearchQueryBuilder()
                .withIndices("document")
                .withTypes("document")
                .withQuery(matchQuery("title", "ONEtWO"))
                .build();

        List<DocumentES> result = elasticsearchTemplate.queryForList(query, DocumentES.class);

        result.forEach (System.out::println);

    }
}

SQL string comparison -how to ignore blank spaces

You can use trim on the column.

where trim(product_type) is null

The above is not DBMS-independent, since Sybase does not provide the trim function.
However, the below approach will work both in Sybase and Oracle:

where rtrim(ltrim(product_type)) is null

---

Related Topics