Caveman's Blog

My commitment to learning.

SQL Server: How to search for a number in a string?

with 2 comments


Following is an efficient way of searching for all numeric values in a string using the PatIndex function:

–Create a table variable
declare @address table(addressId int identity(1,1), addressvalue varchar(50))

–Add some ficticious data
insert into @address values(‘James st, MN’)
insert into @address values(‘James st, CA’)
insert into @address values(‘6 James st, IL’)
insert into @address values(‘5 James st, VA’)
insert into @address values(‘James st, TX’)

–Query to return all the addressvalues with numbers
select addressValue from @address where patindex(‘%[0-9]%’,addressvalue) <> 0

–Result
addressvalue
—————–

6 James st, IL
5 James st, VA

Advertisements

Written by cavemansblog

May 6, 2009 at 6:55 pm

2 Responses

Subscribe to comments with RSS.

  1. […] SQL Server: How to search for a number in a string? 2. SQL Server: how to search for a table name or a column name or a given stored procedure name? […]

  2. […] SQL Server: How to search for a number in a string? […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: