Come dividere i valori di una singola colonna in più valori di colonna?

Ho un problema nel dividere i valori di una singola colonna in più valori di colonna.

Per esempio:

Name ------------ abcd efgh ijk lmn opq asd j. asdjja asb (asdfas) asd asd 

e ho bisogno dell’output qualcosa del genere:

 first_name last_name ---------------------------------- abcd efgh ijk opq asd asdjja asb asd asd null 

Il secondo nome può essere omesso (non è necessario un secondo nome) Le colonne sono già state create e devono inserire i dati da quella singola colonna Name .

Il tuo approccio non gestirà correttamente molti nomi ma …

 SELECT CASE WHEN name LIKE '% %' THEN LEFT(name, Charindex(' ', name) - 1) ELSE name END, CASE WHEN name LIKE '% %' THEN RIGHT(name, Charindex(' ', Reverse(name)) - 1) END FROM YourTable 

Un’alternativa a quella di Martin

 select LEFT(name, CHARINDEX(' ', name + ' ') -1), STUFF(name, 1, Len(Name) +1- CHARINDEX(' ',Reverse(name)), '') from somenames 

Tabella di esempio

 create table somenames (Name varchar(100)) insert somenames select 'abcd efgh' insert somenames select 'ijk lmn opq' insert somenames select 'asd j. asdjja' insert somenames select 'asb (asdfas) asd' insert somenames select 'asd' insert somenames select '' insert somenames select null 

Ciò di cui hai bisogno è una funzione divisa dall’utente. Con quello, la soluzione sembra

 With SplitValues As ( Select T.Name, Z.Position, Z.Value , Row_Number() Over ( Partition By T.Name Order By Z.Position ) As Num From Table As T Cross Apply dbo.udf_Split( T.Name, ' ' ) As Z ) Select Name , FirstName.Value , Case When ThirdName Is Null Then SecondName Else ThirdName End As LastName From SplitValues As FirstName Left Join SplitValues As SecondName On S2.Name = S1.Name And S2.Num = 2 Left Join SplitValues As ThirdName On S2.Name = S1.Name And S2.Num = 3 Where FirstName.Num = 1 

Ecco una funzione di suddivisione del campione:

 Create Function [dbo].[udf_Split] ( @DelimitedList nvarchar(max) , @Delimiter nvarchar(2) = ',' ) RETURNS TABLE AS RETURN ( With CorrectedList As ( Select Case When Left(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End + @DelimitedList + Case When Right(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End As List , Len(@Delimiter) As DelimiterLen ) , Numbers As ( Select TOP( Coalesce(DataLength(@DelimitedList)/2,0) ) Row_Number() Over ( Order By c1.object_id ) As Value From sys.columns As c1 Cross Join sys.columns As c2 ) Select CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen As Position , Substring ( CL.List , CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen , CharIndex(@Delimiter, CL.list, N.Value + 1) - ( CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen ) ) As Value From CorrectedList As CL Cross Join Numbers As N Where N.Value <= DataLength(CL.List) / 2 And Substring(CL.List, N.Value, CL.DelimiterLen) = @Delimiter ) 
 ;WITH Split_Names (Name, xmlname) AS ( SELECT Name, CONVERT(XML,'' + REPLACE(Name,' ', '') + '') AS xmlname FROM somenames ) SELECT xmlname.value('/Names[1]/name[1]','varchar(100)') AS first_name, xmlname.value('/Names[1]/name[2]','varchar(100)') AS last_name FROM Split_Names 

e controlla anche il link sottostante per riferimento

http://jahaines.blogspot.in/2009/06/converting-delimited-string-of-values.html

Ecco come ho fatto questo su un database SQLite:

SELECT SUBSTR(name, 1,INSTR(name, " ")-1) as Firstname, SUBSTR(name, INSTR(name," ")+1, LENGTH(name)) as Lastname FROM YourTable;

Spero che sia d’aiuto.

 SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(rent, ' ', 1), ' ', -1) AS currency, SUBSTRING_INDEX(SUBSTRING_INDEX(rent, ' ', 3), ' ', -1) AS rent FROM tolets