0

How do I select from a table column row where only the result set should contain a table column rows where there are Zero(0) or Zeros(00000) and Not any other. e.g from the table below: i want rows 1,3 and 5 to be returned because they contains Zero(s) as account number. I don't need rows like 2 and 4 to be returned.

Sample data is as below:

+-------+----------+--------+--------------+------------------+---------------+------------+
|  Id   | IdGrower | IdBank | IdBankBranch |  TxtAccountName  | TxtAccountNr  | FlgPayBank |
+-------+----------+--------+--------------+------------------+---------------+------------+
| 1     |    11296 |      9 |            9 | FEDNARY MANGWIRO |000000000000   | No         |
| 2     |      617 |      4 |            4 | SIPIWE MAGADZIRE |10519000000000 | No         |
| 3     |    54272 |      6 |            6 | Obert Mudzingwa  |0              | No         |
| 4     |   5473   |      4 |            4 | Bin              |051            | No         |
| 5     |  7463    |      6 |            6 | Bob              |000            | No         |
+-------+----------+--------+--------------+------------------+---------------+------------+
1
  • Dears, i have edited my question for better clarity.
    – Obert
    Aug 25 '20 at 9:16
1

Something like, if your acctno has empty strings you will need to elaborate this query a bit

create table #t (name varchar(20), acctno varchar(30))

insert #t values ('Tim', '000')
insert #t values ('Jim', '00750')
insert #t values ('Bim', '0000000')

select * from #t where replace(acctno, '0','') = ''
2
  • To handle empty strings, something like: select * from #t where len(replace(acctno, '0','')) = 0 and len(acctno) > 0 can be used
    – Lennart
    Aug 25 '20 at 8:28
  • Thanks, it worked.
    – Obert
    Aug 25 '20 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.