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.Split string on each literal (included in result)
vimrc to detect remote connection
Strange issue with regex matching in perl, alternate attempts match
REGEX reformatting
GPA regex in Perl
Replace string unless between two points
TCL passing lists of regexes through command line
Match a Regular Expression by simple 2 cases:
Regex ignore Find and Replace in Notepad++
Perl: How to match a string that is not in a given string [duplicate]
glob2rx in R to get all cells whose last decimal is 5?
Finding file names without a specified character
Perl: quick replacing of occurrences of multiple words in an array
Extract resolution from string
How do I create a Scala Regex that is compiled using Java Pattern.COMMENTS?
Is there a way to search terms in order with RegexpQuery in lucene?

Categories

HOME
html5-canvas
auth0
load
compiler-errors
design
fpdf
active-directory
jfreechart
windows-10-desktop
research
openframeworks
openui5
asp.net-mvc-5.2
pywin32
media-source
beanstalkd
realm-java
nested-set-model
microphone
liferay-aui
stylesheet
esri
opensuse
clappr
lync
dlopen
html-select
background-process
macvim
caldroid
otroslogviewer
knowledge-management
weebly
visualsvn-server
jpype
adsutil.vbs
uservoice
ctl
htc
view-source
python-module
untagged
db2400
sscanf
evaluate
flattr
data-fitting
telecommunication
restbed
webmail
scrutinizer
jca
storage-duration
filenet-content-engine
clientid
cocoon-gem
servlet-listeners
razor-2
nitrogen
cellular-automata
windward
populate
hebrew
xml.modify
css3pie
antisamy
transport-stream
prudentia
working-directory
sdl.net
measure
treetop
servletunit
mkmapviewdelegate
ryz
project-settings
clipboard-pictures
virtual-server
large-teams
plumtree

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