Need To Search In sql Ignoring the Blank Space in the column Data

1

Hello Every one.
I am trying to search data from table using like with wild cards.
Below is the sample.

Blockquote
select consignername from tbl_packet where consignername like '%i s p%'
which is giving me desired result.
OUTPUT
"I S P"

But however if i write my query as below
select consignername from tbl_packet where consignername like '%isp%'
NO OUTPUT

Datatype of the column is nvarchar(100)

I have a procedure for searching in which i have to implement it, if it is possible.

Any Help would be appreciated

Thanks And Regards

2

Try it like this...

like '%i%s%p%'

3

Ty for the suggestion but the name would be "I S P" not necessary.
I can be like e.g."abc def ghi"
i have a procedure in which i am searching 5 columns

4

So you're looking from the two spaces? In that case...

LIKE '% % %'

5

NOT Exactly can have more spaces as
well

6

That will accommodate as many spaces as you'd like as long as it has at least two. If you still need something different, take a moment to articulate the specifics of what you need.

7

I tried this no match return
select ConsigneeName from tbl_packet where ConsigneeName like '%i sp%'
NO OUTPUT

8

So what? That doesn't even remotely resemble LIKE '% % %'

Now... Please... take a moment to articulate the specifics of what you need... Key work = articulate

9

I tried the query u gave and it is giving the desired results.

select ConsigneeName from tbl_packet where ConsigneeName like '%i%s%p%'
Getting the Output
but however if i try below queries.
select ConsigneeName from tbl_packet where ConsigneeName like '%i%sp%'
select ConsigneeName from tbl_packet where ConsigneeName like '%is%p%'
NO OUTPUT

10

If you tried it my way and it worked, why is there still a conversation?

If you're asking why like '%i%sp%' & like '%is%p%' don't work... It's because you're asking that, in the 1st one, "sp" are in the string, next to each other, with no spaces in between... In the 2nd, you're asking for "is" together, without any spaces.

1 Like
11

Since you already cannot utilize an index for this search - and it appears that you are concerned about spaces in the ConsigneeName, why not just eliminate the spaces altogether?

select ConsigneeName from tbl_packet where replace(ConsigneeName, ' ', '') like '%isp%'
1 Like
12

Ty For the Explanation @Jason_A_Long. problem solved:grinning:

1 Like
13

Will try this as well:slightly_smiling_face: thank you