December 11, 2008 at 12:38 pm
Hi,
I am looking for a more elegant way to filter numbers out of names. This works but costs a lot:
select usercode as [User#], UserName [User Name], Email from DTBL_Users where UserName<>''
and username not in (select username from dbo.DTBL_Users where username like '%1%')
and username not in (select username from dbo.DTBL_Users where username like '%2%')
and username not in (select username from dbo.DTBL_Users where username like '%3%')
and username not in (select username from dbo.DTBL_Users where username like '%4%')
and username not in (select username from dbo.DTBL_Users where username like '%5%')
and username not in (select username from dbo.DTBL_Users where username like '%6%')
and username not in (select username from dbo.DTBL_Users where username like '%7%')
and username not in (select username from dbo.DTBL_Users where username like '%8%')
and username not in (select username from dbo.DTBL_Users where username like '%9%')
and username not in (select username from dbo.DTBL_Users where username like '%0%')
and usercode<641
order by usercode
Thanks
December 11, 2008 at 12:48 pm
Clarification please. Do you want to select only rows without numbers or do you want to return all strings, only with numbers removed?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 11, 2008 at 12:52 pm
I don't want any names (rows) like "HOLD UNTIL 10-18-05" or "BLACK-MAIER101709"
December 11, 2008 at 1:01 pm
More elegant (actually lesser lines of code - not the most elegant) would be:
select usercode as [User#], UserName [User Name], Email from DTBL_Users where UserName not like '%[0-9]%'
December 11, 2008 at 1:08 pm
Cool, thanks!
December 11, 2008 at 1:16 pm
Winash, I would have gone with not like '%[0-9]%' being most elegant. What have you got that tops it?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 11, 2008 at 1:42 pm
I don't have anything that tops the "not like %[0-9]%" 🙂
I only said it might not be the most elegant since someone else might come up with a more elegant solution...heh...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply