Select alphabets from a string value

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

Transact-SQL (2005)

Select alphabets from a string value

Author

Topic

kumar1248
Starting Member

20 Posts

Posted - 2009-04-01 : 15:47:58

Hi,

I am looking for a string function to output alphabetical values from a string that contains both alphabets and Numbers.
For ex: Column1 has value of 'AB CA 92585' then i want output as ABCA or AB CA

please help.

Thanks,
Kumar.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-01 : 15:53:50

SELECT SUBSTRING(Col1, PATINDEX('%[0-9]%', Col1) - 1)
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-04-01 : 23:53:01

Hi try this once,

declare @a varchar(40),@start int, @end int ,@number varchar(1024), @sting varchar(1024),@var varchar(1)
select @a = 'AB CA 92585'
SELECT @end = datalength(@a),@start = 1 ,@number = '',@sting = ''
while ( @start <= @end)
BEGIN
SELECT @var = substring(@a,@start,1)
IF isnumeric(@var) = 1
SET @number = @number+@var
ELSE
SET @sting = @sting+@var
SELECT @start = @start+1
END
select @number as Number ,@sting as string

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-02 : 04:28:51

If you want to get characters in all positions
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/18/extract-only-numbers-from-a-string.aspx

Change [0-9] to [a-zA-Z]

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources