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

Transact-SQL (2005)

Data Scrubbing

Author

Topic

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-09-26 : 05:08:29

Dear All,
Allow me to use that, what I want to achieve is if I have a varchar field, call NAME. I want to manipulate the data under that field that I will get different result.. i.e

NAME ==> NAME2
ALEX ==> LAEX

Doing this with c# or vb, pose no problem, how can do this using t-sql.

Thanks all

I sign for fame not for shame but all the same, I sign my name.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 05:11:17

[code]SELECT NAME,
SUBSTRING(NAME,2,1) + LEFT(NAME,1) + SUBSTRING(NAME,3,LEN(NAME)) AS NAME2
FROM YourTable[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 05:18:37

[code]DECLARE @Sample TABLE
(
Name VARCHAR(200),
Name2 VARCHAR(200)
)

INSERT @Sample
(
Name
)
SELECT 'Alex' UNION ALL
SELECT 'SQLTeam' UNION ALL
SELECT 'Peso'

SELECT s.Name,
w.t
FROM @Sample AS s
CROSS APPLY (
SELECT SUBSTRING(s.Name, Number, 1)
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.Number BETWEEN 1 AND DATALENGTH(s.Name)
ORDER BY NEWID()
FOR XML PATH('')
) AS w(t)[/code]

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

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-09-26 : 05:21:17

Thanks Bro,

Can we tweak this to work for any length greater than four. I expect the NAME field length to be greater than 4 and <= 255.

Thanks Bro, You gave an insight...

I sign for fame not for shame but all the same, I sign my name.

raky
Aged Yak Warrior

767 Posts

Posted - 2008-09-26 : 05:26:09

quote:
Originally posted by abacusdotcom

Thanks Bro,

Can we tweak this to work for any length greater than four. I expect the NAME field length to be greater than 4 and <= 255.

Thanks Bro, You gave an insight...

I sign for fame not for shame but all the same, I sign my name.

Yes, u can do for the length mentioned above.

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-09-26 : 05:28:45

Thanks Peso, you are wonderful...
Thanks for the help bro...

Is there another way we can manipulate data to give new series, am only trying seek for more alternatives, am ok with the first solution..

After sqlteam forumn, we also learn from others.. lol

Thanks

I sign for fame not for shame but all the same, I sign my name.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-26 : 05:32:32

UPDATE Table1
SET Name2 = REVERSE(Name)

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

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2008-09-26 : 05:40:00

@Peso... hmmmmmmmmmmmmmmmmm!

I sign for fame not for shame but all the same, I sign my name.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources