如何從單個列中決議多個值？問題是多個值被無關的（就我而言）資料包圍。例子：
Select * from my_table:
Fname Lname Data
Fred Smith @3aXXXX;Name:AA;@43deXXXX;Name:BB;@5433ed9;NAME:ABC;*#!XXXXXXXX;NAME:MyPetDog;@#IDXXXX
對于資料列，我想從列中提取“名稱：”欄位后面的所有值。那將是“名稱：”之后和“;”之前的文本 . 在上面的例子中：
Select Fname, Lname, [DATA] from my_table
Fname Lname [*Parsed* DATA]
Fred Smith AA,BB, ABC, MyPetDog
解決上述問題將是一個巨大的幫助。但是，我真正想做的是查找/替換（SWITCH 等）從字串回傳的每個值：
Fname Lname [Translated DATA]
Fred Smith Airport, Bus Station, Restaurant, FIDO
對于使用名義資料，我深表歉意。我的實際腳本（在另一個網路上）涉及幾個表連接以到達列 DATA。我只是不知道如何從這個大字串中提取特定值（其他的是提取完整資料集并使用 AWk 或 MS Excel 之后清理資料）。
感謝有關解決此問題的任何幫助或提示。
凱文 L。
uj5u.com熱心網友回復：
我建議創建一個小的 VBA 函式，該函式接受“丑陋”的資料并使用該Split函式將其拆分。就像是：
Public Function fSplitData(strData As String) As String
Dim aData() As String
Dim lngLoop1 As Long
aData = Split(strData, ";")
For lngLoop1 = LBound(aData) To UBound(aData)
If Left(aData(lngLoop1), 5) = "Name:" Then
fSplitData = fSplitData & Mid(aData(lngLoop1), 6) & ","
End If
Next lngLoop1
If Right(fSplitData, 1) = "," Then fSplitData = Left(fSplitData, Len(fSplitData) - 1)
End Function
這給出了所需的輸出：
AA,BB,ABC,MyPetDog
而且，您不僅可以連接提取的值，還可以使用記錄集從查找表中獲取資料。在您的情況下，由于您只有 8 個值，因此您可以If在代碼中使用：
Public Function fSplitData(strData As String) As String
Dim strLookup As String
Dim aData() As String
Dim lngLoop1 As Long
aData = Split(strData, ";")
For lngLoop1 = LBound(aData) To UBound(aData)
If Left(aData(lngLoop1), 5) = "Name:" Then
strLookup = Mid(aData(lngLoop1), 6)
If strLookup = "AA" Then
fSplitData = fSplitData & "Airport,"
ElseIf strLookup = "BB" Then
fSplitData = fSplitData & "Bus Station,"
ElseIf strLookup = "ABC" Then
fSplitData = fSplitData & "Restaurant,"
ElseIf strLookup = "MyPetDog" Then
fSplitData = fSplitData & "FIDO,"
End If
End If
Next lngLoop1
If Right(fSplitData, 1) = "," Then fSplitData = Left(fSplitData, Len(fSplitData) - 1)
End Function
然后，您可以在查詢中使用此函式，就像使用標準 Access 函式一樣。所以你的 SQL 看起來像：
SELECT
FName,
LName,
Data,
fSplitData([Data]) AS TranslatedData
FROM Table1;
問候，
uj5u.com熱心網友回復：
感謝@Applecore，我有一個幾乎可行的解決方案（雖然可能不是最好的解決方案）：
SELECT Fname,
Lname,
SWITCH(data like "*AA*","Airport")&", "&
SWITCH(data like "*BB*", "Bus Station")&", "&
SWITCH(data like "*ABC*", "Restaurant")&", "&
SWITCH(data like "*MyPetDog*","FIDO") as DATA
Fname Lname [Translated DATA]
Fred Smith Airport, Bus Station, Restaurant, FIDO
唯一的問題是，如果一個值不存在，那么我會得到額外的逗號。例如，如果 BB 是唯一存在的值，那么我得到：
Fname Lname [Translated DATA]
Fred Smith , Bus Station, ,
uj5u.com熱心網友回復：
按照@AppleCore 的建議做，使用外部函式來避免混亂。
SELECT
Fname,
Lname,
CleanField([Data]) AS TranslatedData
FROM
my_table;
將函式（Select Case ..用于轉換）復制粘貼到一個新模塊中，轉到選單Debug, Compile，然后將該模塊保存為 Module1_：
Public Function CleanField(ByVal Value As String) As String
Dim Parts As Variant
Dim Index As Integer
Dim Part As String
Dim Text As String
Parts = Split(";" & Value, ":")
For Index = LBound(Parts) To UBound(Parts)
Part = Split(Parts(Index), ";")(0)
Select Case Part
Case Is = "AA"
Part = "Airport"
Case Is = "BB"
Part = "Bus station"
Case Is = "ABC"
Part = "Restaurant"
Case Is = "MyPetDog"
Part = "FIDO"
End Select
If Part <> "" Then
If Text <> "" Then
Text = Text & ", "
End If
Text = Text & Part
End If
Next
CleanField = Text
End Function
例子：
Fname Lname TranslatedData
------- ------- --------------------------------------
Fred Smith Airport, Bus station, Restaurant, FIDO
George Olsen Airport, Restaurant, Bus station, FIDO
Tina Doe Restaurant, Bus station, FIDO
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/378775.html
下一篇：SQL：從表中洗掉，以視圖為源