SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Data Scrubbing
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

abacusdotcom
Posting Yak Master

Nigeria
133 Posts

Posted - 09/26/2008 :  05:08:29  Show Profile  Visit abacusdotcom's Homepage  Send abacusdotcom a Yahoo! Message  Reply with Quote
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

India
52325 Posts

Posted - 09/26/2008 :  05:11:17  Show Profile  Reply with Quote
SELECT NAME,
SUBSTRING(NAME,2,1) + LEFT(NAME,1) + SUBSTRING(NAME,3,LEN(NAME)) AS NAME2
FROM YourTable
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 09/26/2008 :  05:18:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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)



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

abacusdotcom
Posting Yak Master

Nigeria
133 Posts

Posted - 09/26/2008 :  05:21:17  Show Profile  Visit abacusdotcom's Homepage  Send abacusdotcom a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

raky
Aged Yak Warrior

767 Posts

Posted - 09/26/2008 :  05:26:09  Show Profile  Reply with Quote
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.
Go to Top of Page

abacusdotcom
Posting Yak Master

Nigeria
133 Posts

Posted - 09/26/2008 :  05:28:45  Show Profile  Visit abacusdotcom's Homepage  Send abacusdotcom a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30277 Posts

Posted - 09/26/2008 :  05:32:32  Show Profile  Visit SwePeso's Homepage  Reply with Quote
UPDATE Table1
SET Name2 = REVERSE(Name)



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

abacusdotcom
Posting Yak Master

Nigeria
133 Posts

Posted - 09/26/2008 :  05:40:00  Show Profile  Visit abacusdotcom's Homepage  Send abacusdotcom a Yahoo! Message  Reply with Quote
@Peso... hmmmmmmmmmmmmmmmmm!

I sign for fame not for shame but all the same, I sign my name.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000