• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 111
  • Last Modified:

SQL - loop and add string to end

select shiptoname  from customershipto

This return 10000 names.

How can I loop in customershipto table and change from customer name to customer name & "_number" at the end.

Sample Customershipto table return

Medical
Hopital
FieldMed

Change to

Medical_1
Hopital_2
FieldMed_3
etc...
0
VBdotnet2005
Asked:
VBdotnet2005
  • 2
  • 2
  • 2
1 Solution
 
ste5anSenior DeveloperCommented:
E.g.

SELECT	name + '_' + CAST(ROW_NUMBER() OVER ( ORDER BY name ) AS VARCHAR(255) )
FROM	sys.tables
ORDER BY name;

Open in new window

0
 
VBdotnet2005Author Commented:
I am so sorry. I want to do update instead of select. For every row on customer name, I want to update like my sample above.
0
 
VBdotnet2005Author Commented:
Customer_1
Customer_1
Customer_1
Etc...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ste5anSenior DeveloperCommented:
Then just update it..

 
DECLARE @Sample TABLE ( Payload NVARCHAR(255) );

INSERT INTO @Sample
VALUES	( 'A' ),
	( 'B' ),
	( 'C' );

WITH Data AS (
	SELECT	Payload,
		'_' + CAST(ROW_NUMBER() OVER ( ORDER BY Payload ) AS VARCHAR(255) ) AS Suffix
	FROM	@Sample
	)
	UPDATE	Data
	SET	Payload = Payload + Suffix;

SELECT	*
FROM	@Sample;

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
VBdotnet2005, do you still need help with this question?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please, don't accept my last comment as an answer. You need to accept the comments that REALLY helped you to solve the issue.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now