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.
| Author |
Topic |
|
groth.j
Starting Member
6 Posts |
 Posted - 2007-10-03 : 11:01:09
|
| I am completely new to SQL and I have some syntax questions. I am trying to concatenate 4 fields and some padded constants to form a new key field to perform joins. The result should be a twelve character field without spaces. My problem is that the current fields use spaces as place holders and I need to replace the spaces with ‘0’. [RD_ID] nvarchar length 5 [RDWY_ID] nvarchar length 1 [MLGE_TYPE] nvarchar length 1 [OVLAP_MLGE_CD] nvarchar length 1 Concatenate 12 characters1st ‘0’ (constant)2nd, 3rd, and 4th, from [RD_ID] (without the suffix)5th and 6th from [RD_ID] suffix or replace spaces with ‘00’7th 1 or 2 from [RDWY_ID]8th Z from [MLGE_TYPE] or replace space with ‘0’9th 1 – 9 from [OVLAP_MLGE_CD] or replace space with ‘0’10th ‘S’ (constant)11th ‘0’ (constant)12th ‘0’ (constant)Results should resemble 0001CQ100S00 or 000100100S00This is the query I used in Access.LRS: "0" & IIf((Len(LTrim(RTrim([HITIS2_TVCLEAR2]![RD_ID])))=3),LTrim(RTrim([HITIS2_TVCLEAR2]![RD_ID])) & "00",LTrim(RTrim([HITIS2_TVCLEAR2]![RD_ID]))) & [HITIS2_TVCLEAR2]![DIR_RDWY_ID] & IIf([HITIS2_TVCLEAR2]![MLGE_TYPE]=" ",0,[HITIS2_TVCLEAR2]![MLGE_TYPE]) & IIf([HITIS2_TVCLEAR2]![OVLAP_MLGE_CD]=" ",0,[HITIS2_TVCLEAR2]![OVLAP_MLGE_CD]) & "S00"Thanks for any help. |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-10-03 : 11:06:35
|
| use substring function. + contencates the strings_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
groth.j
Starting Member
6 Posts |
Posted - 2007-10-03 : 11:41:00
|
| I have used this query but it returns a result with spaces. I need to return a result with '0' in place of the spaces.('0'+[RD_ID]+[RDWY_ID]+[MLGE_TYPE]+[OVLAP_MLGE_CD]+'S00')I have also tried this query for the first column.('0'+rtrim([RD_ID])+'00')This works great where the last two characters are spaces. where the last two charachters are not spaces I end up with two extra charachters '00'.I am not sure how the subtract function will help me. However, I may just not understand how it works. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-03 : 11:46:04
|
I can't see SUBSTRING there at all?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-03 : 11:49:36
|
[code]SELECT REPLACE('0' + LEFT(RD_ID + '000', 3) + RIGHT('00' + RD_ID, 2) + LEFT(RDWY_ID + '0', 1) + LEFT(MLGE_TYPE + '0', 1) + LEFT(OVLAP_MLGE_CD + '0', 1) + 'S00', ' ', '0')FROM Table1[/code]
E 12°55'05.25"N 56°04'39.16" |
|
|
|
groth.j
Starting Member
6 Posts |
Posted - 2007-10-03 : 12:12:48
|
| I used the query and checked my results this worked great thank you very much. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-10-04 : 01:36:13
|
| If you want to show formatted data in front end do concatenation thereMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Concatenation