Postgresql में साफ स्ट्रिंग
मेरे पास एक तालिका में एक स्तंभ है जिसमें नीचे प्रारूप में किसी कंपनी के बारे में परिवर्तनों से संबंधित किसी भी अपडेट के बारे में डेटा है -
#=============#==============#================#
| Company ID | updated_at | updates |
#=============#==============#================#
| 101 | 2020-11-01 | name: |
| | | -ABC |
| | | -XYZ |
| | | url: |
| | | -www.abc.com |
| | | -www.xyz.com |
+-------------+--------------+----------------+
| 109 | 2020-10-20 | rating: |
| | | -4.5 |
| | | -4.0 |
+-------------+--------------+----------------+
जैसा कि आप ऊपर देख सकते हैं, कॉलम updatesमें तार शामिल हैं जिनमें नईलाइन शामिल हैं और एक या कई अपडेट का वर्णन है। उपरोक्त उदाहरण में इसका मतलब है कि कंपनी आईडी 101 के लिए, नाम एबीसी से एक्सवाईजेड में बदल गया और यूआरएल www.abc.com से www.xyz.com में बदल गया । कंपनी आईडी 109 के लिए, केवल रेटिंग 4.5 से 4.0 में बदल गई।
हालाँकि मैं अपडेट कॉलम को 3 कॉलम में विभाजित करना चाहूंगा - एक में वह होना चाहिए जो बदल गया था (url, नाम आदि), दूसरे में पुराना मान होना चाहिए और तीसरे कॉलम में नया मूल्य होना चाहिए। कुछ इस तरह -
#============#============#==============#================#
| Company ID | Field | Old Value | New Value |
#============#============#==============#================#
| 101 | name | ABC | XYZ |
+------------+------------+--------------+----------------+
| 101 | url | www.abc.com | www.xyz.com |
+------------+------------+--------------+----------------+
| 109 | rating | 4.5 | 4.0 |
+------------+------------+--------------+----------------+
मैं Postgres में ऐसा कर रहा हूं और जानता हूं कि वर्णों के आधार पर सबस्ट्रिंग कैसे निकाले जाते हैं, लेकिन यह मुझे थोड़ा जटिल लगता है क्योंकि मुझे प्रत्येक पंक्ति के लिए एक ही कॉलम से कई सबस्ट्रिंग निकालने की जरूरत होती है। किसी भी सहायता की सराहना की जाएगी। धन्यवाद!
जवाब
सबसे पहले, आप regexp_split_into_tableअपनी तालिका के एक संस्करण को प्राप्त करने के लिए एक पॉजिटिव लुकहैड के साथ एक रेगीक्स का उपयोग कर सकते हैं जिसमें प्रत्येक पंक्तियों में ठीक एक अपडेट है:
select companyID,
updated_at,
regexp_split_to_table(updates, '\n(?=\y.+:)') as updates
from old;
यह कॉलम updatesको किसी भी नईलाइन ( \n) पर विभाजित करेगा , जिसके बाद एक शब्द और एक कॉलोन ( \y.+:) होगा।
#=============#==============#================#
| companyID | updated_at | updates |
#=============#==============#================#
| 101 | 2020-11-01 | name: |
| | | -ABC |
| | | -XYZ |
+-------------+--------------+----------------+
| 101 | 2020-11-01 | url: |
| | | -www.abc.com |
| | | -www.xyz.com |
+-------------+--------------+----------------+
| 109 | 2020-10-20 | rating: |
| | | -4.5 |
| | | -4.0 |
+-------------+--------------+----------------+
इससे, आप अपनी इच्छित तालिका को अधिक आसानी से बना सकते हैं। ऐसा करने के लिए, आप उदाहरण के split_partलिए अद्यतन स्ट्रिंग को तीन भागों में विभाजित करने के लिए उपयोग कर सकते हैं जो आप चाहते हैं।
पहले भाग को एक साथ रखने पर आपको पूर्ण क्वेरी मिलती है:
select companyID,
updated_at,
split_part(updates, E':', 1) as field,
split_part(updates, E'\n-', 2) as old_value,
split_part(updates, E'\n-', 3) as new_value
from (select companyID,
updated_at,
regexp_split_to_table(updates, '\n(?=\y.+:)') as updates
from old
)
;
यहाँ एक db <> फिडेल उदाहरण है:https://dbfiddle.uk/?rdbms=postgres_10&fiddle=92017c8f296a0d100fd856eef835e60d
अधिक जानकारी / अतिरिक्त जानकारी:
- न्यूट्रेल कैरेक्टर पोस्टग्रैजिंग स्ट्रिंग्स में: https://stackoverflow.com/a/26638775/14015737
- postgresql regex शब्द सीमाएँ: https://stackoverflow.com/a/3825705/14015737
- नए स्तंभों में तारों को विभाजित करना: https://stackoverflow.com/a/8612456/14015737