Inserting a character '&' within a string

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

Transact-SQL (2008)

Inserting a character '&' within a string

Author

Topic

vkap
Starting Member

3 Posts

Posted - 2012-07-02 : 14:58:14

hi all..

I am trying to insert a character '&' within a string .

for example: data column is somewhat like this;
1. 2/3 @ 1.5ml 1@200ul
2. 2/3 @ 1.5ml & 1@200ul
3. 1/2 @ 200ul @ 5/5@200ul
4. 4

Result set:
1. 2/3 @ 1.5ml & 1@200ul
2. 2/3 @ 1.5ml & 1@200ul
3. 1/2 @ 200ul & 5/5@200ul
4. 4

These are different kind of string, i tried to use 'STUFF' but it didnt work as the characters length is not fixed. any suggesstions??

thanks a lot!
vkap

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-07-02 : 17:21:26

my suggestion is save each value in it's own column then you can index it, query analyze etc. as you have it now it is all text and you are asking for issues later on.

otherwise you can try this


declare @bootleg table(whachyousay varchar(max))


insert into @bootleg
select '2/3 @ 1.5ml 1@200ul'
union
select  '2/3 @ 1.5ml & 1@200ul'
union
select '1/2 @ 200ul @ 5/5@200ul'
union
select '4'

select whachyousay,
       REPLACE(REPLACE(REPLACE(whachyousay,'ml ', 'ml & '), 'ml & & ', 'ml & '), 'ul @', 'ul &')
From @bootleg

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-02 : 19:33:00

unless you've consistent data format, only available option is to take each format as a batch and apply a logic to add the & to it separately.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-07-03 : 12:04:58

best approach would be to save each data piece to it's own table.

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources