0

Possible Duplicate:
Most efficient T-SQL way to pad a varchar on the left to a certain length?

Im having some trouble with syntax trimming with an sql statement. Here is my sql statement that I am failing miserably at : 

 SqlCommand command = new SqlCommand("SELECT PID = TRIM([PID] & Right("00000000" & TRIM ([PID]),8) & ",END"), FROM dbo.MDRMASTER WHERE PARENTPID = @PARENTPID",con);

As you can see, Ive been experimenting all over the place, but before I butchered my SQL Statement, the values I returned this : 

 123456123456123456

I want to return this : 

 00123456,00123456,00123456,END

So what is the proper syntax to separate values by commas, add a word on the end of the statement, and to sort the values into a certain format. For example the numbers should all be 8 digits. Whether the return is 00000123 or 01234567. Any help or a point in the right direction would be greatly appreciated.

Community
  • 1
  • 1
javasocute
  • 648
  • 2
  • 11
  • 28

1 Answers1

1

The SQL that would work for this is

DECLARE @pidList VARCHAR(2000);

SELECT @pidList = COALESCE(@pidList + ',', '') + REPLICATE('0', 8 - DATALENGTH(MDRMASTER.PID)) + MDRMASTER.PID
FROM dbo.MDRMASTER
WHERE MDRMASTER.PARENTPID = @PARENTPID;

SELECT @pidList + ',END' AS PID;

Unfortunately I can't test passing this all in one string through a SqlCommand() object, but I don't see why it wouldn't work if you put the commands, separated by semi-colons into one string.

Mike Curtis
  • 88
  • 5