sql server - Updating a column based on first occurance of a text in another column -

-

i have requirement convert 150 char free form text , map 1 of 2 texts: sibling or spouse in member_type field in sql server database.

i came below update statement update:

update my_table set member_type = case       when (relationship_description 'brother%' or             relationship_description 'sister%' or             relationship_description  'sibling%'         'sibling'         when ( relationship_description 'spouse%' or              relationship_description 'husband%' or              relationship_description 'wife%' or        ) 'spouse'       else '' end;

but there additional requirement: if there multiple key words relationship_description, convert using first key word.

for example: case 1: relationship_description = "mark's brother" : contains "brother" in treated sibling.

case2: relationship_description = "walter brother of mark , greg howard. mark husband of julie":first occurance brother treated sibling.

case3:relationship_description = "john's wife": contains keyword wife, should treated spouse

case 4: relationship_description = "johns's wife , peter's sister": contains 2 keywords wife , sister, should consider wife should treated spouse.

i came know there sql keyword stuff might work. can help?. need via sql script , not via java.

try pathindex function:

declare @t table(relationship_description varchar(max), member_type varchar(10)) insert @t values ('mark''s brother in', null), ('walter brother of mark , greg howard. mark husband of julie', null), ('john''s wife', null), ('johns''s wife , peter''s sister', null)  update t     set member_type = case when m = 0                             null else                                      case when ca.t = 1                                           'sibling'                                           else 'spouse'                                      end                        end @t t cross apply (select top 1 *              (values              (patindex('%brother%', relationship_description), 1),               (patindex('%sister%', relationship_description), 1),               (patindex('%sibling%', relationship_description), 1),               (patindex('%spouse%', relationship_description), 2),               (patindex('%husband%', relationship_description), 2),               (patindex('%wife%', relationship_description), 2)) t(m, t)              order row_number() over(order case when m = 0 1000000 else m end))ca  select * @t

in cross apply finding out indexes of first occurrences of key words. picking minimal index different 0(0 means there no occurrence, mark 1000000). in main case verifying if there no occurrence of word select null else @ occurrence type , select appropriate description.

output:

relationship_description                                                  member_type mark's brother                                                            sibling walter brother of mark , greg howard. mark husband of julie   sibling john's wife                                                               spouse johns's wife , peter's sister                                           spouse

Comments

Post a Comment

Popular posts from this blog

Java 3D LWJGL collision -

-
i making 3d java game lwjgl library, , wondering how add collision detection, player not go through models. i using obj models. here objloader class, loads models: package renderengine; import java.io.bufferedreader; import java.io.file; import java.io.filenotfoundexception; import java.io.filereader; import java.util.arraylist; import java.util.list; import models.rawmodel; import org.lwjgl.util.vector.vector2f; import org.lwjgl.util.vector.vector3f; public class objloader { public static rawmodel loadobjmodel(string filename, loader loader){ filereader fr = null; try { fr = new filereader(new file("res/"+filename+".obj")); } catch (filenotfoundexception e) { system.err.println("couldn't load file!"); e.printstacktrace(); } bufferedreader reader = new bufferedreader(fr); string line; list<vector3f> vertices = new arraylist<vector3f...
Read more

spring - SubProtocolWebSocketHandler - No handlers -

-
i have ugly error during deploying spring app on jboss. 18:11:16,025 error [org.apache.catalina.core.containerbase.[jboss.web].[default-host].[/consumer]] (msc service thread 1-7) exception sending context initialized event listener instance of class org.springframework.web.context.contextloaderlistener: org.springframework.context.applicationcontextexception: failed start bean 'subprotocolwebsockethandler'; nested exception java.lang.illegalargumentexception: no handlers @ org.springframework.context.support.defaultlifecycleprocessor.dostart(defaultlifecycleprocessor.java:176) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor.access$200(defaultlifecycleprocessor.java:51) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor$lifecyclegroup.start(defaultlifecycleprocessor.java:346) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.s...
Read more

methods - python can't use function in submodule -

-
my folder structure this: pythonstuff/ program.py modulea/ __init__.py foo.py bar.py here's code bar.py : def hello(): print("hello, world!") here's code program.py : #!/usr/bin/env python3 modulea import bar bar.hello() i run $ python3 program.py somehow error: file "program.py", line 3, in <module> bar.hello() attributeerror: 'module' object has no attribute 'hello' edit: __init__.py file empty. edit2: after trying realized had bar.py in root directory contained hello() method. bar.py in modulea/ directory empty. add it import os __all__ = [] module in os.listdir(os.path.dirname(__file__)): if module != '__init__.py' , module[-3:] == '.py': __all__.append(module[:-3]) the init .py files required make python treat directories containing packages; done prevent directories common name, such string, unintentionally hiding v...
Read more