SQL Server – How to Select Rows with Zero or Zeros in a Column

sql serversql-server-2016

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         |
+-------+----------+--------+--------------+------------------+---------------+------------+

Best Answer

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','') = ''

Related Solutions

Sql-server – Select multiple rows from one row based on column values

I'd use UNPIVOT for this unless abr and ubr are both indexed and a relatively low proportion contain the value 1.

(Borrowing JNK's table variable)

SELECT id,
       Edition
FROM @prod
UNPIVOT (V FOR Edition IN (abr,ubr)) AS Unpvt
WHERE V = 1

Sql-server – Retrieve value of select query in a table column

I've tested the following (in SQL Server 2014, but it should work at least as far back at 2008), and I believe it does what you want:

CREATE TABLE QueryList (Id int, Name nvarchar(128), Query nvarchar(4000));

INSERT INTO QueryList
VALUES (1, 'Date', 'SELECT GETDATE()')
      ,(2, 'String', 'SELECT ''What''''s this? Just a varchar string...'' as StrVal')
      ,(3, 'Server', 'SELECT @@SERVERNAME')
;

CREATE TABLE results (Id int, Name nvarchar(128), Result nvarchar(4000));

-----

DECLARE @stmt nvarchar(max);

SELECT @stmt = stuff( (SELECT N'UNION ALL SELECT TOP (1) ' + CAST(Id as nvarchar(10))
                             +N' as Id, ''' + Name + N''', CAST(qr.[Result] as nvarchar(4000)) as [Result] '
                             +N'FROM (' + Query + N') AS qr ([Result])
'
                         FROM QueryList
                          FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
                     ,1,9,'INSERT INTO results') + ';'
;

SELECT @stmt; -- FOR TESTING - displays the resulting query

EXECUTE sp_executesql @stmt;

SELECT * FROM results;

The central query takes all the records from #QueryList, and builds an INSERT statement to place the id, name, and the query result from each row into the #results temp table. Each row's values are in their own SELECT statement, and all the statements are combined into a single result set with UNION ALL. I have taken text files with thousands of rows, converted each one into a similar SELECT ... UNION ALL, and used that to successfully insert thousands of records into SQL Server tables; I don't expect the number of statements being unioned together to be an issue (but I can't guarantee there isn't a limit at some point).

If you haven't seen it before, this uses FOR XML PATH to concatenate the UNION ALL SELECT ... from each row's values into a single string, the STUFF replaces the first UNION ALL with the actual INSERT INTO #results. See this article for more information on aggregating string data across multiple rows (or, move to SQL 2017 and use STRING_AGG()).

Something else that you may not have seen before is FROM (<subquery>) AS qr ([Result]). Just as the AS qr makes qr and alias for the subquery, ([Result]) provides a list of aliases for the columns (in our case, column) in the subquery.

Here's the query that's built from the above data:

INSERT INTO #results SELECT TOP (1) 1 as Id, 'Date', CAST(qr.[Result] as nvarchar(4000)) as [Result] FROM (SELECT GETDATE()) AS qr ([Result])
UNION ALL SELECT TOP (1) 2 as Id, 'String', CAST(qr.[Result] as nvarchar(4000)) as [Result] FROM (SELECT 'This is just a varchar string' as StrVal) AS qr ([Result])
UNION ALL SELECT TOP (1) 3 as Id, 'Server', CAST(qr.[Result] as nvarchar(4000)) as [Result] FROM (SELECT @@SERVERNAME) AS qr ([Result])
;

And, the contents of #results after the query is executed:

Id   Name     Result
---- -------- --------------------------------------
1    Date     Jul 14 2017 12:19PM
2    String   What's this? Just a varchar string...
3    Server   MYSERVERNAME\INSTANCE

Notes:

If your query cannot trivially be used as a subquery (for instance, if you had a query in #QueryList that used a CTE), this will fail.
If you included a closing ; at the end of a query in #QueryList, it will fail (special case of not being trivially used as a subquery; I almost included this in my #QueryList queries, by force of habit).
To be on the safe side, this allows for the possibility that some query may return multiple rows, and simply selects the first row as the "right" value, at random.
If you do run into an issue with the number of statements being combined via UNION ALL, you can break this down into smaller chunks. Assuming that Id and Name taken together, are unique in #QueryList, then:
```
SELECT @stmt = stuff( (SELECT TOP (10000) N'UNION ALL SELECT TOP (1) ' + CAST(ql.Id as nvarchar(10))
                                        +N' as Id, ''' +ql. Name + N''', CAST(qr.[Result] as nvarchar(4000)) as [Result] '
                                        +N'FROM (' + ql.Query + N') AS qr ([Result])
'
                         FROM #QueryList ql
                                LEFT  JOIN #results r ON (ql.Id = r.Id AND ql.Name = r.Name)
                        WHERE r.Id IS NULL
                          FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
                     ,1,9,'INSERT INTO results') + ';'
;
```
would let you grab 1000 #QueryList rows at a time, and only grab ones where the Id and Name don't already exist in #results. You'd have to put this in a loop, and keep going until there were no more rows left in #QueryList. @stmt is NULL if there are no more rows to process, so you could use that to break out of the loop.

Again, I don't expect this to be an issue. And, even if it is, while it takes you back to a WHILE loop, you're dealing with 10,000 (or more) rows at a time, not one. It should be faster than your current cursor implementation.

And - if you find that there are some queries that cause problems, this could be useful for testing; go through subsets of data until you find a set that doesn't work, then check those queries to see if there's an issue with one.

Best Answer

Related Solutions

Sql-server – Select multiple rows from one row based on column values

Sql-server – Retrieve value of select query in a table column

Related Question