Split a value in one column to two columns

问题:

I have a column in SQL that I would like to split a column into two columns on select (by a delimiter). For example, the table currently has:

---------
Mary - 16

I'd like two columns as the result of the query:

--------   --------
Mary       16

Thanks for your help.

回答1:

SELECT 
  left_side  = RTRIM(SUBSTRING(col, 1, CHARINDEX('->', col)-2)),
  right_side = LTRIM(SUBSTRING(col, CHARINDEX('->', col) + 2, 4000))
FROM dbo.table;

Ah, I see. | characters are column specifiers, not part of the output. Try:

SELECT 
  left_side  = LTRIM(RTRIM(SUBSTRING(col, 1, CHARINDEX('-', col)-1))),
  right_side = LTRIM(RTRIM(SUBSTRING(col, CHARINDEX('-', col) + 1, 4000)))
FROM dbo.table;

回答2:

If your not worried about edge cases, something like this will work:

Declare @Var varchar(200)
SET @Var = 'Mary - 16'

SELECT LEFT(@Var, PATINDEX('%-%', @Var) - 1), Right(@Var, LEN(@Var) - PATINDEX('%-%', @Var))

Just change @Var to your field name in the query.