Updating a table, setting a substring using another table via Sub-Select

Question

I am currently using a query, however sometimes it doesn't update all fields. I need a workaround on this. Can anybody help me?

update table1 
set number = 
( 
select substring(number from offset) 
from table2 
where strpos(number,prefix) = '1' 
order by length(prefix) 
desc limit '1' 
) ;

table1:

number
----------
1001123456

table2:

prefix | offset
-------+-------
1001   | 5

My goal, hopefully, is to retrieve only the substring (in this example "123456" for every row in table1 that matches the specific prefix in table2.

Any help would be greatly appreciated! Thank you!

Mosty Mostacho · Accepted Answer · 2012-02-17 04:38:02Z

1

Actually you don't need the offset field.

Table 1:

+------------+
|   NUMBER   |
+------------+
| 1001123456 |
|   10012222 |
|  200244444 |
|    2003666 |
+------------+

Table 2:

+--------+
| PREFIX |
+--------+
|   1001 |
|  20036 |
+--------+

Query:

select number, prefix,
  cast(
    substring(cast(t1.number as text), 
              char_length(cast(t2.prefix as text)) + 1)
  as integer) as suffix
from t1
join t2 on cast(t2.prefix as text) = 
  left(cast(t1.number as text),
       char_length(cast(t2.prefix as text)))

Result:

+------------+--------+--------+
|   NUMBER   | PREFIX | SUFFIX |
+------------+--------+--------+
| 1001123456 |   1001 | 123456 |
|   10012222 |   1001 |   2222 |
|    2003666 |  20036 |     66 |
+------------+--------+--------+

answered Feb 17, 2012 at 4:38

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JPT Over a year ago

Thank you for the response Mosty! I forgot to mention I am using Postgresql 8.2.1 because we can't update it for now. Although, i'm kind of lost on the cast expressions. It is the same with the "::" correct?... Also, the left(... part seems not to be working on mine. I wonder what went wrong?

Mosty Mostacho Over a year ago

Hey John! Hmm, I don't know much about Postres (I know nothing, actually) but I'm sure that works on version 9. Take a look at this link. I don't know how to cast in Postgres, but cast the way you usually do :) The left seems to be added in version 9 and is actually the same as substring(1, x), I think :) See 8.2 and 9.1

JPT Over a year ago

Moreover, I tried this: select number, prefix, substring(t1.number from length((t2.prefix)::integer + 1)) as suffix from t1 join t2 on t2.prefix = substring(t1.number from 1 for char_length(t2.prefix::int + 1 ));

Collectives™ on Stack Overflow

Updating a table, setting a substring using another table via Sub-Select

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related