MS SQL Server
  1. MS SQL Server
  3. Extrait textuel FREE TEXT de fullname
Intereting Posts
Requête Linq utilisant un groupe et ayant SQL Server: ne pas get la dernière date avec la requête entre Comment supprimer les colonnes de key primaire sans réplique Comment faire une requête d'insertion plus rapide dans c #? Compter les périodes et la durée de maladie dans un roulement de 12 mois Comment gérer l'injection SQL dans le module de noeud mssql Node.js bloque le server SQL lors de la réception de plusieurs requests ADO se connecter à SQL Server échoue lorsque la carte sans fil est utilisée mais le câble vers le réseau fonctionne correctement. utilise lpc:? accélérer la requête SQL comment mettre à jour un logging dans la table pour insert une virgule et une valeur et sa valeur en sql Convertir l'époque en millisecondes en date dans T-SQL create xml schema collection valeur maximale de fractionDigits ressortingction Trouver le nombre de lignes mises à jour dans T-SQL Trop de tables de search Solution de procédure stockée SQL Server

Extrait textuel FREE TEXT de fullname

 
Need help with extracting the firstname, middlename and lastname from a freetext fullname. How to extract them out with all these formats? Need to figure out how to handle format 2,5,9,7 --fullname sample data DECLARE @name TABLE (fullname VARCHAR(100)) INSERT INTO @name SELECT 'Malone,Susan M' UNION ALL SELECT --1 'Conn,Chris G' UNION ALL SELECT --2 'Van Pess,Wen B' UNION ALL SELECT --3 'DESHPANDE, ANN W.' UNION ALL SELECT --4 'Asif,LEE' UNION ALL SELECT --5 'CERVANTES MANDY'UNION ALL SELECT --6 'Bill, Dave' UNION ALL SELECT --7 'SMITH,ANN M' UNION ALL SELECT --8 'BHULLER, MATT' UNION ALL SELECT --9 'KIM (DAUM), GAIL' UNION ALL SELECT --10 'John.Mills'--11 DECLARE @DELIMITER1 varchar(5), @DELIMITER2 varchar(5), @DELIMITER3 varchar(5),@MAX_LENGTH int SET @DELIMITER1 = ',' SET @DELIMITER2 = ' ' SET @MAX_LENGTH = 50 --LastName SELECT fullname, case when CHARINDEX(@DELIMITER2, fullname) >=1 then replace(SUBSTRING(fullname, 1, CHARINDEX(@DELIMITER2, fullname) ),',','')--replace to empty ssortingng if contains a "," when CHARINDEX(@DELIMITER2, fullname) =0 then replace(SUBSTRING(fullname, 1, CHARINDEX(@DELIMITER1, fullname) ),',','')--replace to empty ssortingng if contains a "," else null end as Lastname, --Middle Name CASE -- Middle fullname follows two-fullname first fullnames like Mary Ann WHEN LEN(SUBSTRING(fullname, CHARINDEX(@DELIMITER1,fullname)+ 2,@MAX_LENGTH)) - LEN(REPLACE(SUBSTRING(fullname, CHARINDEX(@DELIMITER1,fullname)+ 2,@MAX_LENGTH), @DELIMITER2, '')) > 0 --when len is greater than 0 THEN SUBSTRING(fullname, LEN(fullname) - CHARINDEX(@DELIMITER2, REVERSE(fullname))+2, @MAX_LENGTH) ELSE NULL END AS Middlefullname, --First Name CASE -- Count the number of @DELIMITER2. Choose the ssortingng between the WHEN LEN(SUBSTRING(fullname, CHARINDEX(@DELIMITER1,fullname)+ 2,@MAX_LENGTH)) - LEN(REPLACE(SUBSTRING(fullname, CHARINDEX(@DELIMITER1,fullname)+ 2,@MAX_LENGTH), @DELIMITER2, '')) > 0 -- --when len is greater than 0 Then replace(lsortingm(SUBSTRING(fullname, CHARINDEX(@DELIMITER1,fullname)+ 1, ---need help here (LEN(SUBSTRING(fullname, CHARINDEX(@DELIMITER1,fullname)+ 2,@MAX_LENGTH))- LEN(SUBSTRING(fullname, LEN(fullname) - CHARINDEX(@DELIMITER2, REVERSE(fullname))+2, @MAX_LENGTH))))),'-','') --replace the "-" to empty ssortingng ELSE lsortingm(SUBSTRING(fullname,CHARINDEX(@DELIMITER1,fullname)+ 1,@MAX_LENGTH))--sortingmmed leading spaces END AS Firstname FROM @name order by fullname

Première logique pour concaténer les "parties du nom" avec des points. Notez mes commentaires. 

 --fullname sample data DECLARE @name TABLE (nameid int identity, fullname VARCHAR(100)) INSERT INTO @name SELECT 'Malone,Susan M' UNION ALL SELECT --1 'Conn,Chris G' UNION ALL SELECT --2 'Van Pess,Wen B' UNION ALL SELECT --3 'DESHPANDE, ANN W.' UNION ALL SELECT --4 'Asif,LEE' UNION ALL SELECT --5 'CERVANTES MANDY'UNION ALL SELECT --6 'Bill, Dave' UNION ALL SELECT --7 'SMITH,ANN M' UNION ALL SELECT --8 'BHULLER, MATT' UNION ALL SELECT --9 'KIM (DAUM), GAIL' UNION ALL SELECT --10 'John.Mills';--11 select original = fullname, prepped = dotted.fn, total.spaces from @name cross apply (values (patindex('%(%),%',fullname),fullname)) prep1(x,fn) -- check for parentheses: cross apply (values ( -- remove parentheses if they exist, replace commas w/ dots, dots w/ spaces: case when prep1.x > 1 then subssortingng(fn,1,x-1) + subssortingng(fn,charindex(',',fn,x)+1,8000) else replace(replace(fn, ',', '.'),'.',' ') end)) prep(fn) cross apply (values (replace(rsortingm(lsortingm(replace(prep.fn,' ',' '))),' ',' '))) clean(fn) cross apply (values (len(clean.fn)-len(replace(clean.fn,' ','')))) total(spaces) -- count spaces cross apply (values (replace(clean.fn, ' ','.'))) dotted(fn); -- replace spaces with dots

Cela renvoie 

 original prepped spaces ------------------------ -------------------- ------- Malone,Susan M Malone.Susan.M 2 Conn,Chris G Conn.Chris.G 2 Van Pess,Wen B Van.Pess.Wen.B 3 DESHPANDE, ANN W. DESHPANDE.ANN.W 2 Asif,LEE Asif.LEE 1 CERVANTES MANDY CERVANTES.MANDY 1 Bill, Dave Bill.Dave 1 SMITH,ANN M SMITH.ANN.M 2 BHULLER, MATT BHULLER.MATT 1 KIM (DAUM), GAIL KIM.GAIL 1 John.Mills John.Mills 1

Le rest peut être fait en utilisant du parsename comme ça: 

 --fullname sample data DECLARE @name TABLE (nameid int identity, fullname VARCHAR(100)) INSERT INTO @name SELECT 'Malone,Susan M' UNION ALL SELECT --1 'Conn,Chris G' UNION ALL SELECT --2 'Van Pess,Wen B' UNION ALL SELECT --3 'DESHPANDE, ANN W.' UNION ALL SELECT --4 'Asif,LEE' UNION ALL SELECT --5 'CERVANTES MANDY'UNION ALL SELECT --6 'Bill, Dave' UNION ALL SELECT --7 'SMITH,ANN M' UNION ALL SELECT --8 'BHULLER, MATT' UNION ALL SELECT --9 'KIM (DAUM), GAIL' UNION ALL SELECT --10 'John.Mills';--11 with clean as ( select original = fullname, prepped = dotted.fn, total.spaces from @name cross apply (values (patindex('%(%),%',fullname),fullname)) prep1(x,fn) -- check for parentheses: cross apply (values ( -- remove parentheses if they exist, replace commas w/ dots, dots w/ spaces: case when prep1.x > 1 then subssortingng(fn,1,x-1) + subssortingng(fn,charindex(',',fn,x)+1,8000) else replace(replace(fn, ',', '.'),'.',' ') end)) prep(fn) cross apply (values (replace(rsortingm(lsortingm(replace(prep.fn,' ',' '))),' ',' '))) clean(fn) cross apply (values (len(clean.fn)-len(replace(clean.fn,' ','')))) total(spaces) -- count spaces cross apply (values (replace(clean.fn, ' ','.'))) dotted(fn) ) select original, cleaned = case spaces when 1 then parsename(prepped,1)+' '+parsename(prepped,2) when 2 then parsename(prepped,2)+' '+parsename(prepped,1)+' '+parsename(prepped,3) when 3 then parsename(prepped,2)+' '+parsename(prepped,1)+' '+parsename(prepped,3)+ ' '+parsename(prepped,4) end from clean

Résultats: 

 original cleaned -------------------- ------------------ Malone,Susan M Susan M Malone Conn,Chris G Chris G Conn Van Pess,Wen B Wen B Pess Van DESHPANDE, ANN W. ANN W DESHPANDE Asif,LEE LEE Asif CERVANTES MANDY MANDY CERVANTES Bill, Dave Dave Bill SMITH,ANN M ANN M SMITH BHULLER, MATT MATT BHULLER KIM (DAUM), GAIL GAIL KIM John.Mills Mills John

Alternativement, vous pouvez utiliser un regex clr (mdq.regexreplace). Notez mon article sur SPC il y a quelques années.