如何将String拆分为两列

I Have a table like below

我有一张如下表

 Property   Value

 Adapter    [00000007] Intel(R) 82567LM Gigabit Network Connection
 Adapter    [00000009] VMware Virtual Ethernet Adapter for VMnet1
 Adapter    [00000012] Dell Wireless 1397 WLAN Mini-Card

from above table i want to split values column string into two columns like below is:

从上面的表我想将列字符串拆分为两列,如下所示:

String1        String2
[00000007]     Intel(R) 82567LM Gigabit Network Connection
[00000009]     VMware Virtual Ethernet Adapter for VMnet1

Any solution please.

请解决任何问题。

4 个解决方案

#1

select 
  substring(Value,charindex('[',Value,1),charindex(']',value,charindex('[',Value,1)-1)) as String1
  ,ltrim(rtrim(substring(Value,charindex(']',value)+1,len(value)))) as String2
from Table1

SQL Fiddle

#2

As the question has changed this is no longer relevant but may prove useful to others...

随着问题的改变,这已不再适用,但可能对其他人有用......

This seems to work:

这似乎有效:

declare @value varchar(100) = '[00000007] Intel(R) 82567LM Gigabit Network Connection'

SELECT 
SUBSTRING(@value, 0, CHARINDEX(']', @value,0) + 1) firstPart,
LTRIM(RTRIM(SUBSTRING(@value, CHARINDEX(']', @value,0)+1, LEN(@value)))) secondPart

#3

If your case simple one will work:

如果您的案例简单,那将是有效的:

select
    Property,
    substring(Value, 2, 8) as String1,
    -- or substring(Value, 1, 10) if you want [ and ]
    right(Value, len(Value) - 11) as String2
from Table1

sql fiddle demo

sql小提琴演示

#4

This is a bit more generic version to support for any number of characters before the first space (Fiddle demo):

这是一个更通用的版本,以支持第一个空格之前的任意数量的字符(小提琴演示):

SELECT LEFT(@S, CHARINDEX(' ',@S)) String1,
       RIGHT (@S, LEN(@S) - CHARINDEX(' ',@S)) String2

Applying to your Table:

申请你的表:

SELECT LEFT(Value, CHARINDEX(' ',Value)) String1,
       RIGHT (Value, LEN(Value) - CHARINDEX(' ',Value)) String2
FROM yourTable

#1

select 
  substring(Value,charindex('[',Value,1),charindex(']',value,charindex('[',Value,1)-1)) as String1
  ,ltrim(rtrim(substring(Value,charindex(']',value)+1,len(value)))) as String2
from Table1