trevor1940
asked on
postgreSQL query
I have 2 tables in posgreSQL something like this
I need to link the 2 tables together so on
table_1
if text1 is 2 digits I need to add a leading zero = temp_text1
and remove the leading zero to text2 = temp_text2
without altering the text in text1 or text2 as these are linked to another tables
then run an update command
update table_1 set text3 = concatenate(temp_text1, '/', temp_text2)
Text3 in both table should then be the same format
xxx/xxxxx or 123/12345
table_1
id,text1,text2,text_3
1,99,012345
2,999,023456
table_2
id,text3
1,099/12345
2,999/23456 I need to link the 2 tables together so on
table_1
if text1 is 2 digits I need to add a leading zero = temp_text1
and remove the leading zero to text2 = temp_text2
without altering the text in text1 or text2 as these are linked to another tables
then run an update command
update table_1 set text3 = concatenate(temp_text1, '/', temp_text2)
Text3 in both table should then be the same format
xxx/xxxxx or 123/12345
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Bill
Yes everything is type text
I'll have a go in the morning
Thanx
Yes everything is type text
I'll have a go in the morning
Thanx
ASKER
Hi Bill
In the version of PostgreSQL I have (8.4) the 'RIGHT' function doesn't exists
so I used
UPDATE table_1 SET text3 = LPAD(text1, 3, '0') || CASE WHEN length(text2) >5 THEN SUBSTR(text2, 2) ELSE text2 END;
Thanx for your help
In the version of PostgreSQL I have (8.4) the 'RIGHT' function doesn't exists
so I used
UPDATE table_1 SET text3 = LPAD(text1, 3, '0') || CASE WHEN length(text2) >5 THEN SUBSTR(text2, 2) ELSE text2 END;
Thanx for your help
Great, glad you sorted that out and got it working.
»bp
»bp
»bp