Listing titles starting with numbers category? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Listing titles starting with numbers category?

Author

Topic

markpjd
Starting Member

6 Posts

Posted - 2014-05-13 : 08:52:05

Hi All,

I'm having problems with my website. I wish to make a section that has names starting with numbers only. For example, with every name beginning with "A", I have this code:

select * from names where name like "a%" order by name

and this works fine, but I want it so it will only list titles starting with 0-9..

Thank you,

Mark.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-05-13 : 09:25:04

[code]
SELECT *
FROM names
WHERE name LIKE '[0-9]%'
ORDER BY name;
[/code]

markpjd
Starting Member

6 Posts

Posted - 2014-05-13 : 09:35:12

Hi Ifor,

I have tried this and it returns no entries, even though they are there. One of the entries is called 2D Demo for example, but this does not appear :\

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-13 : 09:46:28

quote:
Originally posted by markpjd

Hi Ifor,

I have tried this and it returns no entries, even though they are there. One of the entries is called 2D Demo for example, but this does not appear :

Are you sure that the entry begins with the number 2? I ask because I just tried the suggestion like this:


;with names(name) as (select '2D Demo')
SELECT *
FROM names
WHERE name LIKE '[0-9]%'
ORDER BY name;

I got these results:


name
-------
2D Demo

(1 row(s) affected)

Post some of your sample data so we can see what's happening.

markpjd
Starting Member

6 Posts

Posted - 2014-05-13 : 10:14:02

Hi,

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0006 sec)
select * from amigademos where demoname like "[0-9]%" order by demoname;

Demoname is set as varchar.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-05-13 : 10:32:08

MySQL should go like this:
select * from amigademos where demoname RLIKE '[0-9].*'

btw. we are on MS SQL Server only here

Too old to Rock'n'Roll too young to die.

markpjd
Starting Member

6 Posts

Posted - 2014-05-13 : 10:37:28

Hi Webfred,

That does give an output! But lists all demonames with numbers present in them, is there a similar command for listing just those beginning with numbers?

Thank you.

markpjd
Starting Member

6 Posts

Posted - 2014-05-13 : 10:40:32

Hi all,

Got it!

select * from amigademos where demoname RLIKE '^[0-9]' ORDER BY demoname

Thank you for all of your help, you lead me in the right direction!! :)

Kind Regards,

Mark

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-05-13 : 10:42:36

try this:
(in difference to ms sql server the ^sign means here beginning of the string)

select * from amigademos where demoname RLIKE '^[0-9].*'

Too old to Rock'n'Roll too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-05-13 : 10:43:21

Oh! fine

Too old to Rock'n'Roll too young to die.

markpjd
Starting Member

6 Posts

Posted - 2014-05-13 : 10:44:46

Thanks :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources