How to comma to alphanumeric numbers in text

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

How to comma to alphanumeric numbers in text

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-07-18 : 09:42:23

Hi all,

i have a text where it is mix up of all alphanumeric values...
for example like this



ACETAMINOPHEN 250 MG ASPIRIN65 MG CAFFEINE

ACER NEGUNDO POLLEN0.0021 G/ML ACER RUBRUM POLLEN0.0021 G/ML ACER SACCHARINUM POLLEN0.0021 G/ML ACER SACCHARUM POLLEN

So my requirement is i need to add comma (,) before every number value in this text.The text is different from each other.But main one what ever the number is there need to add comma before that one (decimal,numeric etc)



ACETAMINOPHEN,250 MG ASPIRIN,65 MG CAFFEINE

ACER NEGUNDO POLLEN,0.0021 G/ML ACER RUBRUM POLLEN,0.0021 G/ML ACER SACCHARINUM POLLEN,0.0021 G/ML ACER SACCHARUM POLLEN

Suggest me ???

P.V.P.MOhan

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-18 : 10:34:07

For one instance of the pattern you described, you can use the following:

DECLARE @x VARCHAR(1024) = 'ACETAMINOPHEN 250 MG ASPIRIN65 MG CAFFEINE';
SELECT STUFF(@x,PATINDEX('%[^,0-9][0-9]%',@x)+1,0,',');

For multiple instances as you have in the second example, if you follow this approach, you will have to run the update multiple times. There are other fancier ways of doing this - all of them somewhat convoluted. This is because of the poor support SQL Server has for REGEX type of replacements.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 11:05:35

[code]
declare @t varchar(max)='ACER NEGUNDO POLLEN0.0021 G/ML ACER RUBRUM POLLEN0.0021 G/ML ACER SACCHARINUM POLLEN0.0021 G/ML ACER SACCHARUM POLLEN'

SELECT @t=STUFF(@t,PATINDEX('%[A-za-z][0-9]%',@t)+1,0,',')
FROM master..spt_values v
WHERE v.type='p'
AND v.number BETWEEN 1 AND LEN(@t)
AND SUBSTRING(@t,v.number-1,1) LIKE '[A-Za-z]'
AND SUBSTRING(@t,v.number,1) LIKE '[0-9]'

SELECT @t

output
---------------------------------
ACER NEGUNDO POLLEN,0.0021 G/ML ACER RUBRUM POLLEN,0.0021 G/ML ACER SACCHARINUM POLLEN,0.0021 G/ML ACER SACCHARUM POLLEN

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-18 : 11:52:44

quote:

Originally posted by visakh16


declare @t  varchar(max)='ACER NEGUNDO POLLEN0.0021 G/ML ACER RUBRUM POLLEN0.0021 G/ML ACER SACCHARINUM POLLEN0.0021 G/ML ACER SACCHARUM POLLEN'

SELECT @t=STUFF(@t,PATINDEX('%[A-za-z][0-9]%',@t)+1,0,',')
FROM master..spt_values v
WHERE v.type='p'
AND v.number BETWEEN 1 AND LEN(@t)
AND SUBSTRING(@t,v.number-1,1) LIKE '[A-Za-z]'
AND SUBSTRING(@t,v.number,1) LIKE '[0-9]'

SELECT @t



output
---------------------------------
ACER NEGUNDO POLLEN,0.0021 G/ML ACER RUBRUM POLLEN,0.0021 G/ML ACER SACCHARINUM POLLEN,0.0021 G/ML ACER SACCHARUM POLLEN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

If you've a Numbers table you can utilize that too instead of spt_values
I generally dont prefer using system objects like spt_value especially in production. even if no Numbers table is present, I usually create a tally table for this purpose as in Jeff's article

http://www.sqlservercentral.com/articles/T-SQL/62867/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 14:34:11

quote:
Originally posted by James K

quote:
Originally posted by visakh16

declare @t varchar(max)='ACER NEGUNDO POLLEN0.0021 G/ML ACER RUBRUM POLLEN0.0021 G/ML ACER SACCHARINUM POLLEN0.0021 G/ML ACER SACCHARUM POLLEN'

SELECT @t=STUFF(@t,PATINDEX('%[A-za-z][0-9]%',@t)+1,0,',')
FROM master..spt_values v
WHERE v.type='p'
AND v.number BETWEEN 1 AND LEN(@t)
AND SUBSTRING(@t,v.number-1,1) LIKE '[A-Za-z]'
AND SUBSTRING(@t,v.number,1) LIKE '[0-9]'

SELECT @t

output
---------------------------------
ACER NEGUNDO POLLEN,0.0021 G/ML ACER RUBRUM POLLEN,0.0021 G/ML ACER SACCHARINUM POLLEN,0.0021 G/ML ACER SACCHARUM POLLEN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

If you've a Numbers table you can utilize that too instead of spt_values
I generally dont prefer using system objects like spt_value especially in production. even if no Numbers table is present, I usually create a tally table for this purpose as in Jeff's article

http://www.sqlservercentral.com/articles/T-SQL/62867/

yep...I agree
I just posted it as an illustration

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=186615
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-07-19 : 01:40:42

thanks for replying james and visakh.But here i posted only single.i have around 9000 rows different from each other if in one row it's like ''ACETAMINOPHEN 250 MG ASPIRIN65 MG CAFFEINE'' and in another row it is like 'ACER NEGUNDO POLLEN0.0021 G/ML ACER RUBRUM POLLEN0.0021 G/ML ACER SACCHARINUM POLLEN0.0021 G/ML ACER SACCHARUM POLLEN'.So how to get comma before numbers in every row...

P.V.P.MOhan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-19 : 02:15:49

Just extend the given solution for your table


CREATE FUNCTION StuffDelimiter
(
@Delimiter varchar(10),
@String varchar(max)
)
RETURNS varchar(max)
AS
BEGIN 
DECLARE @ret varchar(max)

SET @ret = @String

SELECT @ret = STUFF(@ret,PATINDEX('%[A-za-z][0-9]%',@ret)+1,0,@Delimiter)
FROM master..spt_values v
WHERE v.type='p'
AND v.number BETWEEN 1 AND LEN(@ret)
AND SUBSTRING(@ret,v.number-1,1) LIKE '[A-Za-z]'
AND SUBSTRING(@ret,v.number,1) LIKE '[0-9]'

RETURN (@ret)
END


then call it like

SELECT ColumnName,dbo.StuffDelimiter(',',ColumnName) AS ChangedColumn
FROM YourTable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources