regex


search for specific characters within column and then create different columns from it


I have param_Value column that have different values. I need to extract these values and create columns for all of them.
|PARAM_NAME |param_Value |
__________|____________
|Step 4 | SP:0.09 |
|Procedure | MAX:125 |
|Step 4 | SP:Ambient|
|(null) | +/-:N/A |
|Steam | SP:2 |
|Step 3 | MIN:0 |
|Step 4 | RDPHN427B |
|Testing De | N/A |
I only want columns with: And give them names:
SP: SET_POINT_VALUE,
MAX: MAX_LIMIT,
MIN: MIN_LIMIT,
+/-: UPPER_LOWER_LIMIT
So what I have so far is:
CREATE OR REPLACE FORCE VIEW PROCESS_STEPS
("PARAM_NAME", "SET_POINT_VALUE", "UPPER_LOWER_LIMIT", "MAX_VALUE", "MIN_VALUE")
AS
SELECT PARAM_NAME,
REGEXP_LIKE("param_Value", 'SP:') SET_POINT_VALUE,
REGEXP_LIKE("param_Value", '+/-:') UPPER_LOWER_LIMIT,
REGEXP_LIKE("param_Value", 'MAX:') MAX_VALUE,
REGEXP_LIKE("param_Value", 'MIN:') MIN_VALUE
FROM PROCESS_STEPS
;
I'm more familiar with TSQL and MySQL, but this ought to do what I think you're looking for. If it doesn't exactly, it should at least point you in the right direction.
CREATE OR REPLACE FORCE VIEW PROCESS_STEPS
("PARAM_NAME", "SET_POINT_VALUE", "UPPER_LOWER_LIMIT", "MAX_VALUE", "MIN_VALUE")
AS
SELECT PARAM_NAME
, CASE WHEN "param_Value" LIKE 'SP:%'
THEN SUBSTR("param_Value", INSTR("param_Value", ':')+1)
ELSE Null
END SET_POINT_VALUE
, CASE WHEN "param_Value" LIKE '+/-:%'
THEN SUBSTR("param_Value", INSTR("param_Value", ':')+1)
ELSE Null
END UPPER_LOWER_LIMIT
, CASE WHEN "param_Value" LIKE 'MAX:%'
THEN SUBSTR("param_Value", INSTR("param_Value", ':')+1)
ELSE Null
END MAX_VALUE
, CASE WHEN "param_Value" LIKE 'MIN:%'
THEN SUBSTR("param_Value", INSTR("param_Value", ':')+1)
ELSE Null
END MIN_VALUE
FROM PROCESS_STEPS
;
The basic concept here is identifying the information you want via LIKE, then using SUBSTR and INSTR to extract it. While LIKE is normally something to stay away from, since there's no leading % in your case, it's Sargable, and thus probably not a total efficiency sink.
Really, though, I have to ask you to question why you're laying out your data like this - substring operations are slow in any language, and a DB is no exception. Why not use another column for your limit type? Why not lay it out in the view you're currently looking at?

Related Links

Regex number range target [duplicate]
VB.NET Regular expression 0-9 and a-z not working?
How to remove duplicate strings in Notepad++/EditPad?
Regex - Split string to group
Sed command strange thing
Proxypass exclude URLs that ends with a pattern
Removing 18 characters after a word on each line via Sublime Text
Regex group being too greedy
Postgres regexp replace not working
Converting text log file with data in quotes to individual columns, using RegEx
Splitting and select string starting with uppercase letter with Regex
Regular expression to return only alpha characters left of a character
Notepad++ replace last symbol in search regex
how to enforce a date format
Regex: txt vs tx?
Grok pattern for logs sent to logstash

Categories

HOME
math
actions-on-google
dom
vr
angular2-forms
crf
spring-roo
quickblox
streaming
oracle-adf
activerecord
superfish
development-environment
scrollbar
signature
plots.jl
w2ui
teraterm
lookup-tables
swig
line-breaks
hough-transform
urlrewriter.net
mediator
background-subtraction
yii1.x
spring-batch-admin
reformatting
kprobe
stl
skyscanner
lifecycle
gradient-descent
api-doc
powerbuilder-conversion
perl-module
vue2
cookiestore
response
google-slides
essbase
gradle-tooling-api
karnaugh-map
distributed-lock
gretl
invalidate
sql-insert
ewsjavaapi
jquery-masonry
okular
complement
canopen
abbyy
maven-versions-plugin
easyhook
sqlite-net
rails-console
mit-scheme
building
cglib
sizeof
insert-update
visual-build-professional
network-traffic
groupbox
sliding
gcloud-java
storage-duration
administrator
xelement
kinto
multer
spl
voting
xsb
google-swiffy
byte-order-mark
antiforgerytoken
java-client
nolio
httpservice
publish-actions
cpp-netlib
azimuth
modalpopupextender
qt4.7
siblings
sysadmin
buildout
nsusernotificationcenter
mms-gateway
slimbox
square-cube
haar-wavelet
cakephp-2.1
mysql-backup
rpn
asp.net-session
teamcity-7.0
arr
media-manager
server.mappath
settings.bundle
android-assets
textboxlist
treetop
remote-control
polyglot
markitup
code-statistics
stsadm
usenet
roguelike

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App