MSSQL - 将一个字段拆分为 3 个字段 [英] MSSQL - Split a field into 3 fields

问题描述

我有一个由 1 列组成的结果集，在本例中为 2 行，单列 [ProductDescription] 是一个 varchar 字段，其中包含 3 条信息(我没有设计它)我需要获取这三条信息使用查询分为 3 个附加字段

之前

<前>/------------------------------\|产品描述 ||------------------------------||DB1 - DB2 - DB3 ||数据位1 - 数据位2 - 数据位3|\------------------------------/

之后

<前>/---------------------------------------------------------\|字段 1 |字段 2 |字段 3 |产品描述 ||---------------------------------------------------------||DB1 |DB2 |DB3 |DB1 - DB2 - DB3 ||DataBit1|DataBit2|DataBit3|DataBit1 - DataBit2 - DataBit3|\---------------------------------------------------------/

我曾尝试使用 Substring 和 charindex 的组合，但未能完全正确，字段的每个部分都可以是任意长度，因此使用硬编码偏移不起作用.

解决方案

这并不漂亮，但它确实有效，并且确实为您提供了您正在寻找的内容，针对您的特定情况...如果您有一个可变数字ProductDescription 中的令牌数量，您可能需要创建一个存储过程来在解析字符串时管理您的状态，因为这会很快变得难以管理.

create table #table(productdescription varchar(255))走/* 演示它在漂亮"的情况下工作 */INSERT INTO #TABLE (ProductDescription) 值 ('abc - def - ghi')走/* 演示它在丑陋"的情况下工作 */插入 #table (ProductDescription) 值 ('jklsaf -mnoa-psdfaqr')走SELECT RTRIM(LTRIM(SUBSTRING(ProductDescription, 0, CHARINDEX('-', ProductDescription)-1))) 作为 Field1,RTRIM(LTRIM(SUBSTRING(ProductDescription, CHARINDEX('-', ProductDescription)+1, (CHARINDEX('-', ProductDescription, CHARINDEX('-', ProductDescription)+1)) - (CHARINDEX('-', ProductDescription)+1)))) 作为 Field2，RTRIM(LTRIM(SUBSTRING(ProductDescription, CHARINDEX('-', ProductDescription, CHARINDEX('-', ProductDescription)+1)+1, LEN(ProductDescription)))) as Field3从#table走

我希望这会有所帮助！

I have a resultset consisting of 1 column and in this case 2 rows the single column [ProductDescription] is a varchar field that hold 3 pieces of information (I didn't design it) i need to get those three pieces of information out into 3 additional fields using a query

before

/------------------------------\
|ProductDescription            |
|------------------------------|
|DB1 - DB2 - DB3               |
|DataBit1 - DataBit2 - DataBit3|
\------------------------------/

After

/---------------------------------------------------------\
|Field1  |Field2  |Field3  |ProductDescription            |  
|---------------------------------------------------------|  
|DB1     |DB2     |DB3     |DB1 - DB2 - DB3               |  
|DataBit1|DataBit2|DataBit3|DataBit1 - DataBit2 - DataBit3|  
\---------------------------------------------------------/

I have tried using combinations of Substring and charindex but haven't been able to get it quite right, each part of the field could be any length so using hardcoded offsets doesn't work.

解决方案

This isn't pretty, but it works and it does give you what you are looking for, for your specific case... If you had a variable number of tokens in your ProductDescription, you would probably need to create a stored proc to manage your state while parsing the string, as this would quickly grow unmanageable.

create table #table(productdescription varchar(255))
go
/* Demonstrate it working in a 'pretty' case */
INSERT INTO #TABLE (ProductDescription) values ('abc - def - ghi')
go

/* Demonstrate it working in a 'ugly' case */
insert into #table (ProductDescription) values ('jklsaf -mnoa-psdfaqr')
go

SELECT RTRIM(LTRIM(SUBSTRING(ProductDescription, 0, CHARINDEX('-', ProductDescription)-1))) as Field1,

RTRIM(LTRIM(SUBSTRING(ProductDescription, CHARINDEX('-', ProductDescription)+1, (CHARINDEX('-', ProductDescription, CHARINDEX('-', ProductDescription)+1)) - (CHARINDEX('-', ProductDescription)+1)))) as Field2,

RTRIM(LTRIM(SUBSTRING(ProductDescription, CHARINDEX('-', ProductDescription, CHARINDEX('-', ProductDescription)+1)+1, LEN(ProductDescription)))) as Field3
FROM #table
go

I hope this helps!

这篇关于MSSQL - 将一个字段拆分为 3 个字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！