CODEWITHSUNDEEP
sqlsql-server-2008t-sql
luke_t
luke_t

Reputation: 2985

WHERE varchar = variable length of 0's

Table_A

ID    Number
--    ------
1     0
2     00
3     0123
4     000000
5     01240
6     000

The 'Number' column is data type varchar.

EDIT for clarity. My question is, can I easily pull back all rows of data which contain a variable length string of 0's?

I have tried:

SELECT *
FROM Table_A
WHERE LEFT(Number,1) = '0' AND RIGHT(Number,1) = '0'

Using the above, it would still return the below, using the example table provided.

ID    Number
--    ------
1     0
2     00
4     000000
5     01240
6     000

I was looking for a function which I could pass the LEN(Number) int into, and then it generates a string of a specfic character (in my case a string of 0's). I wasn't able to find anything though.

Oh, and I also tried adding a SUBSTRING to the WHERE clause, but sometimes the Number column has a number which has a 0's in the middle, so it still returned strings with other numbers except only 0.

SUBSTRING(Number,ROUND(LEN(Number)/2,0),1) = '0'

Any help is appreciated.

Upvotes: 0

Views: 108

Answers (5)

Phani
Phani

Reputation: 93

You can use this following one also,you will get your expected result.

SELECT *

FROM Table_A

WHERE Nunber not like '%[1-9]%'

Thanks.

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

SELECT *
FROM yourtable
WHERE len(Number) - len(replace(number,'0','')) >= 0

One more approach

Upvotes: 0

Md Mahfuzur Rahman
Md Mahfuzur Rahman

Reputation: 2359

Your can use this query :

SELECT * FROM Table_A WHERE Number LIKE '%0%';

It'll solve your problem.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239714

So, you want a string that doesn't contain anything that isn't a 0? Sounds like it's time for a double-negative:

SELECT *
FROM Table_A
WHERE NOT Number like '%[^0]%'
AND number like '0%' --But we do want it to contain at least one zero

(The final check is so that we don't match the empty string)

Upvotes: 4

dani herrera
dani herrera

Reputation: 51675

Answer:

Where number like '%0%'

Upvotes: 1

Related Questions