Manipulating strings - SQL Server Forums

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 Administration (2000)

Manipulating strings

Author

Topic

monty
Posting Yak Master

130 Posts

Posted - 2006-10-31 : 20:52:35

Hi All,

I need a script that will manipulate string values, like names for example, to change them to another value. I don't want a script that changes all values in a character column to the same value. What I'm looking for is a script that will change that column in each row to a different value. The purpose is to protect the identity of the original name in the row but to still maintain some uniqueness between rows.
looking forward 4ur help

monty

its me monty

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-31 : 20:58:18

You have to provide more information for us to help you. Like table strucutre, sample data and the expected result

monty
Posting Yak Master

130 Posts

Posted - 2006-10-31 : 21:28:24

khtan,

only this basic idea is given to me even iam not aware of the structure due to some security reasons iam supposed to just give a rough idea or a procedure to move on frm this basic idea...

plz help me out

its me monty

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-31 : 21:43:12

"due to some security reasons"
You don't have to post the actual table structure. Just a simple structure with data that can illustrate your requirement.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-31 : 21:59:30

"I need a script that will manipulate string values, like names for example, to change them to another value. I don't want a script that changes all values in a character column to the same value. What I'm looking for is a script that will change that column in each row to a different value. The purpose is to protect the identity of the original name in the row but to still maintain some uniqueness between rows."

Basically you can look up the string manipulation function on Books OnLine.
Like replace(), left(), substring(), right() etc. Can't help you much without knowing specific requirement.

monty
Posting Yak Master

130 Posts

Posted - 2006-11-02 : 21:06:44

Hi Khtan,
suppose that coloumn has:
1:cat
2:bat
3:mat

it should be changed to something like :
1:cmaot(cats new value)
2:bmaot(bats new value)
its just an example

some thing like encrption and we need to preserve the unquieness
looking 4all ur help

its me monty

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-02 : 21:22:13

if it is some sort of one to one replacement, you can use a mapping table to do it


-- Your original table
create table #table1
(
	col	varchar(10)
)
-- the mapping table that stores what change to what
create table #mapping
(
	old	varchar(10),
	new	varchar(10)
)
-- insert the sample data for testing
insert into #table1
select	'cat'	union all
select	'bat'	union all
select	'mat'

insert into #mapping
select	'cat', 'cmaot'	union all
select	'bat', 'bmaot'

-- before update
select	* from #table1

-- update with mapping
update	t
set	col	= m.new
from	#table1 t inner join #mapping m
	on	t.col	= m.old

-- after update
select	* from #table1

monty
Posting Yak Master

130 Posts

Posted - 2006-11-02 : 21:52:04

Hi khtan,

thanks alot for such a quick response... i did test the code it looks gud, but it cld be gr8 if u cld help me out with a code where:
1)first it parses a coloumn(varchar) in each row
2)replaces that coloumn with a random value(character)
3)retrives the modified coloumn of each row
basically the idea here is to preserve the uniqueness of each coloumn..to be precise similar to encryption..

iam xreamly sorry if i mislead u earliear..
thanks in advance

its me monty

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-02 : 22:07:30

cld be iam xreamly gr8 if u cld xpln gud en inglis. thx

CODO ERGO SUM

monty
Posting Yak Master

130 Posts

Posted - 2006-11-02 : 22:15:10

what do u mean?
infact i dont understand ur english..

its me monty

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-02 : 22:29:15

quote:
Originally posted by monty

what do u mean?
infact i dont understand ur english..

its me monty

cld u xpln y? i wud b xreamly gr8fool.

CODO ERGO SUM

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-11-03 : 03:32:42

quote:
Originally posted by monty
infact i dont understand ur english..

I don't think they spoke English in Ur; it's more probable that the early settlers spoke Sumerian.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-03 : 03:40:13

Well, substitution is not encryption...

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-03 : 21:05:05

quote:
Originally posted by Arnold Fribble

quote:
Originally posted by monty
infact i dont understand ur english..

I don't think they spoke English in Ur; it's more probable that the early settlers spoke Sumerian.

Ur was inhabited in the Ubaid period around 5300 BCE, while it is only known that the Sumerian language was spoken by about 4000 BCE, so it's hard to be sure.

CODO ERGO SUM

monty
Posting Yak Master

130 Posts

Posted - 2006-11-04 : 08:07:09

Hi khtan,

Thanks for ur help, i got the script for which i was looking
please have a look at it :

DECLARE @ROWID INT, @I INT, @UPDATED_VALUE VARCHAR(150), @NEW_VALUE INT, @CHECK INT

SET @I = 1

SET @CHECK = (SELECT COUNT(*) FROM TEST)

SET @RowID = 1

WHILE (@ROWID < @CHECK+1)

BEGIN

SET @UPDATED_VALUE = (SELECT STUFF(LNAME, 2, 4, RAND(@ROWID)) FROM TEST WHERE ID = @ROWID)

UPDATE Test SET LNAME = @UPDATED_VALUE WHERE ID = @ROWID

SET @ROWID = @ROWID + 1

END

--select COUNT(*) from test_table

--SELECT * FROM TEST

its me monty

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources