Searching for records that begin with a number

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 2000 Forums

SQL Server Development (2000)

Searching for records that begin with a number

Author

Topic

hmusa
Starting Member

36 Posts

Posted - 2002-04-02 : 02:39:20

I'm sure this is very easy but cannot find it anywhere on the site.
I want to select all records that begin with a number to f , I can construct the "A" to "F" part but how do I include in my select statement the numeric records.

thanxs in advance

Nazim
A custom title

1408 Posts

Posted - 2002-04-02 : 03:02:13

Am not sure what you are asking for. if you meant searching records starting with a specific number in a column.

here it is

select * from tablename where columnname like '4%'

It will work for both numeric and character datatype.

HTH

--------------------------------------------------------------

hmusa
Starting Member

36 Posts

Posted - 2002-04-02 : 03:07:47

This is what I am looking for but I thought there might be an easy way to select all records that start with a number instead of writing a select statement with a like clause for every number I am searching for.

Nazim
A custom title

1408 Posts

Posted - 2002-04-02 : 03:28:15

you can create a sp and do it easily

create procedure (@startnotosearch int)
as
set nocount on
declare @strSql as varchar(500)
select @strSql='select * from tablename like '''+ltrim(str(@a))+'%'''
exec (@strSql)
Go

Remember this will be slower comparatively coz it is using Dynamic Sql and Dynamic Sql doesnt uses Chache plan.

--------------------------------------------------------------

Teroman
Posting Yak Master

115 Posts

Posted - 2002-04-02 : 04:36:25

--avoid dynamic SQL, just do this

create procedure myProc
@n int

as

set nocount on

select * from myTable
where myColumn like rtrim(str(@n))+ '%'

Go

also avoids a couple of typos ;)

col

Nazim
A custom title

1408 Posts

Posted - 2002-04-02 : 07:37:57

Hmusa, Teremon stored procedure does the trick , replace rtrim with ltrim .
they inadverently creep up, cant help