sql statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

sql statement

Author

Topic

ntn104
Posting Yak Master

175 Posts

Posted - 2014-03-10 : 12:39:11

hello,

How do I write sql statement to select only those records that displayed 9 digits of ID #. Because the table showed some like 1, 2012....198...ect...but I wanted to display only ID = 9 digits.

Please advise,

select id
from table
where id <>''
and id <>'000000000'

Thanks,

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-10 : 12:47:25

Did you mean you want to display prefixed zero's if there are fewer than 9 digits in the ID?

SELECT RIGHT('000000000'+CAST(id AS VARCHAR(16)),9) FROM .....

ntn104
Posting Yak Master

175 Posts

Posted - 2014-03-10 : 13:46:45

No, I wanted to get result of those accounts that have id = 9 digits only, not with less than 9 digits.

For example, if ID = 123, or ID=2012 ---> DO NOT SELECT/DISPLAY
if ID = 9 digits such as ID =300456789, then display as result.

I think I may have to put substr(id,1,9) on a select statement.
Thanks,

quote:
Originally posted by James K

Did you mean you want to display prefixed zero's if there are fewer than 9 digits in the ID?
SELECT RIGHT('000000000'+CAST(id AS VARCHAR(16)),9) FROM .....

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-10 : 15:17:38

If the data type of id column is numeric type, add a where clause that is like this:

WHERE
	id >= 100000000
	AND id <= 999999999

If it is a character column, add a where clause like this:

WHERE
	LEN(id) = 9

ntn104
Posting Yak Master

175 Posts

Posted - 2014-03-11 : 08:13:24

The ID is character, I did use len(id)=9 in where clause, but still not working...not sure why.. Thanks,

quote:
Originally posted by James K

If the data type of id column is numeric type, add a where clause that is like this:
WHERE
id >= 100000000
AND id <= 999999999
If it is a character column, add a where clause like this:
WHERE
LEN(id) = 9

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-11 : 08:28:58

Can you give some more information? When you said it is not working, what did you mean? Is it giving you a syntax error, or no results, or incorrect results, or something else?

If you post the question with examples where it does not work, that would make it easier to respond. See here: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

ntn104
Posting Yak Master

175 Posts

Posted - 2014-03-11 : 08:57:18

I guess I find out why the len function not working since the field ID set up for 9 character, but those invalid IDs are in the middle of the field (within the 9 characters allowance). Please see some example below:

1
00
16
74
99
127
164
172
202
202
291
889
918
0004
0012
0022
0022
0088
0105
0132
0132
0181
0236
0260
0292
0292
0350
0377
0377
0411
0412
0450
0546
0571
0594
0631
1043419
1142420
1142420

I tried this where id <>' 1 ' (to give spaces in front of the #1 and after the #1 enough for 9 characters)
and it works, however we can't do for all the scenarios...> thousand times ....I am trying to figure out the trim method on left and right...but not sure yet...

Thanks,

quote:
Originally posted by James K

Can you give some more information? When you said it is not working, what did you mean? Is it giving you a syntax error, or no results, or incorrect results, or something else?

If you post the question with examples where it does not work, that would make it easier to respond. See here: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-11 : 10:00:31

Replace function might help you in this case...

e.g. Len(Replace(ColumnName,' ',''))=9

And if there are non numeric characters others than spaces and your requirements are only to look for numeric with the length of 9 characters, then you might need to apply a pattern search

WHERE Replace(ColumnName,' ','') like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Cheers
MIK

ntn104
Posting Yak Master

175 Posts

Posted - 2014-03-11 : 11:26:09

Thanks, this method works.

quote:
Originally posted by MIK_2008

Replace function might help you in this case...

e.g. Len(Replace(ColumnName,' ',''))=9

And if there are non numeric characters others than spaces and your requirements are only to look for numeric with the length of 9 characters, then you might need to apply a pattern search

WHERE Replace(ColumnName,' ','') like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources