December 8, 2006 at 8:39 am
Hi,
I'm trying to add a extra spaces to a SQL statement. This is the source for a drop down that I have in an asp.net drop down. I had set ANSI_Padding ON but this is not working. This is the SQL
SELECT DISTINCT activityno + '|' + RTRIM(LTRIM(designation)) AS Activity
FROM tablex
the activityno is set to six characters in length, so the range is between 0 - 999999. However in my asp.net dropdown when I display say 0-10(and so on) It's only displaying one to two characters. Here is an example
Drop down display
0|Name...
10|Name...
What I need to display is
0 |Name
10 |Name
That way when I pass this value into my sproc I can parse it out into my sproc using the field length for correct calculations. I'm using a third party grid control that only allows you to pass either Id or Name.
Thanks,
December 8, 2006 at 8:46 am
Something like this should do it :
SELECT RIGHT(ActivityNo + SPACE(9), 10) + '|' + Designation FROM
(SELECT CONVERT(VARCHAR(10), ActivityNo), RTRIM(LTRIM(Designation)) FROM tableX GROUP BY ActivityNo, RTRIM(LTRIM(Designation))) dtA
December 8, 2006 at 8:58 am
>>I can parse it out into my sproc using the field length
If the purpose of the spaces is simply for field-length based parsing, why not parse using the CharIndex() of the '|' character instead ?
December 8, 2006 at 12:18 pm
You're right the true purpose of this is to parse out the data and load the data into two separate fields. This is what I came up with:
@ConcatenateVariable would equal something like '01 |This is the name of the Id'
UPDATE tableX SET
Number = LEFT(@ConcatenateVariablename,CHARINDEX('|',@Concatenatevariablename) - 1),
Numbername = SUBSTRING(@Concatenatevariablename,5,30),
Record_Update = GETDATE()
WHERE ID = @Id
Let me know if I can refactor this even more.
Thanks,
December 8, 2006 at 6:32 pm
select cast(1 as char(6)) + '|' + 'Name...'
1 |Name...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply