EN VI

Sql-server - Filter the results based on substring in SQL Server?

How to Sql-server - Filter the results based on substring in SQL Server

I have a table

BookNName,Col1

Col1(nvarchar) has data in the following format

BookNo|ShelfNo|BookQty

Col1
-----

1|1|763
2|2|989
3|3|431
4|4|

I have to display only the BookQty and also add a condition that BookQty<900

So, my output should be

BookName BookQty
ABC        763
XYZ        431
WEST            <----------Should dislpay blank value also

What I tried

To display only BookQty, I wrote the following -

select BookName, case when charindex('|',Col1,1)>=2 then right(Col1, charindex('|', reverse(col1))-1)
else ''
end
as 'BookQty'

This is giving output as

BookName BookQty
ABC        763
SOUTH      989
XYZ        431
WEST

Now, I have to add the condition that BookQty<900

For that I tried -

select BookName, case when charindex('|',Col1,1)>=2 then right(Col1, charindex('|', reverse(col1))-1)
else ''
end
as 'BookQty'
from Table1
where right(Col1, charindex('|', reverse(col1))-1)<'900'

This is giving me error

Invalid length parameter passed to the RIGHT function

I know this is due to the blanks in the column. But is there any other way to do this. I cannot change the database values.

Solution:

The real solution is to fix your design. Storing delimited data in your database is bad design, and the problems it causes are many; that you've encountered one today just tells you further to fix your design. If you can't, then get whoever can to fix it, it needs fixing.

As for the problem, you have values without pipes (|); your bad data is inconsistent. Really you should be using NULL, not blank strings (surprise, this error wouldn't occur then either, like if you had a good design). You can, however, check the returned value of CHARINDEX and NULL it if there isn't a pipe (the returned value is 0):

SELECT T1.BookName,
       RIGHT(T1.Col1, NULLIF(CHARINDEX('|', REVERSE(T1.Col1)), 0) - 1) AS BookQty --Don't use literal strings for aliases, they're for literal strings; they have "gotchas" and some syntaxes are deprecated
FROM dbo.Table1 T1
WHERE TRY_CONVERT(int,RIGHT(T1.Col1, NULLIF(CHARINDEX('|', REVERSE(T1.Col1)), 0) - 1)) < '900'; --As it's a string, then try to convert it. We don't want values like "six" or "1K".