問題描述
我有一個由 1 列組成的結(jié)果集，在本例中為 2 行，單列 [ProductDescription] 是一個 varchar 字段，其中包含 3 條信息(我沒有設(shè)計(jì)它)我需要獲取這三條信息使用查詢分為 3 個附加字段
之前
<前>/------------------------------\|產(chǎn)品描述 ||------------------------------||DB1 - DB2 - DB3 ||數(shù)據(jù)位1 - 數(shù)據(jù)位2 - 數(shù)據(jù)位3|\------------------------------/之后
<前>/---------------------------------------------------------\|字段 1 |字段 2 |字段 3 |產(chǎn)品描述 ||---------------------------------------------------------||DB1 |DB2 |DB3 |DB1 - DB2 - DB3 ||DataBit1|DataBit2|DataBit3|DataBit1 - DataBit2 - DataBit3|\---------------------------------------------------------/我曾嘗試使用 Substring 和 charindex 的組合，但未能完全正確，字段的每個部分都可以是任意長度，因此使用硬編碼偏移不起作用.
這并不漂亮，但它確實(shí)有效，并且確實(shí)為您提供了您正在尋找的內(nèi)容，針對您的特定情況...如果您有一個可變數(shù)字ProductDescription 中的令牌數(shù)量，您可能需要創(chuàng)建一個存儲過程來在解析字符串時管理您的狀態(tài)，因?yàn)檫@會很快變得難以管理.
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!
這篇關(guān)于MSSQL - 將一個字段拆分為 3 個字段的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！