CODEWITHSUNDEEP
sqlsql-server
Shannon
Shannon

Reputation: 1

Can you turn a string field into a table

i'm new to this so my terminology may be off sorry.

Ok so I am referencing some tables in SQL which is producing a table like this. (I mostly understand it) You will see the Parameters column has a string in which a list is separated by a Pipe "|" there's also a #"random letter" after the = which I need to sort at a later date as well.

[table][1]

This table is then used some reporting software.

My question is can I split this parameters field into each parameter and value? or just select the specific parameter/s I need or is this a bad idea altogether?

This is my code so far[SQL][2]

Sorry this is the code, ill focus on what i'm after

SELECT [Parts].[Parameters]

FROM [Parts]

which returns this

BAND=#SNNNE|ETR=#L2|SIZE_A=#M1.574803|SIZE_B=#M7.874016|SIZE_C=#M29.527559|SIZE_D=#M47.244094|SIZE_E=#M70.866142|SIZE_F=#M86.614173|SMALL_EDGE_INSET=#M0.590551|EDGE_INSET=#M1.968504|SCREW_DIAMETER=#M0.196850|I_CAM_EDGE_INSET=#M0.374016|H_CAM_EDGE_INSET=#M0.944882|I_CAM_DEPTH=#M0.472441|H_CAM_DEPTH=#M0.472441|I_CAM_DIAMETER=#M0.787402|H_CAM_DIAMETER=#M0.590551|I_PIN_OFFSET=#M0.255906|H_PIN_OFFSET=#M0.314961|I_PIN_DEPTH=#M0.472441|H_PIN_DEPTH=#M0.472441|I_PIN_DIAMETER=#M0.314961|H_PIN_DIAMETER=#M0.196850|H_BORE_DIAMETER=#M0.314961|EDG_NBR=#L4|ED1TYPE_A=#L1|EDGE_INSET_B1=#M1.968504|C_QTY_B1=#M0.118110|QTY_A1=#M0.118110|ED2TYPE_A=#L1|EDGE_INSET_B2=#M1.968504|C_QTY_B2=#M0.118110|QTY_A2=#M0.118110|ED3TYPE_A=#L1|EDGE_INSET_B3=#M1.968504|C_QTY_B3=#M0.118110|QTY_A3=#M0.118110|ED4TYPE_A=#L1|EDGE_INSET_B4=#M1.968504|C_QTY_B4=#M0.118110|QTY_A4=#M0.118110|WINGLINE26=#L1501|WINGLINE230=#L1500|WINGLINE770=#L1502|WINGLINE770B=#L1503|WINGLINE780=#L1499

Upvotes: 0

Views: 86

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82010

There are many split/parse examples available. If 2016 you can use String_Split()

The following is an in-line example

Example

Select A.ID
      ,Item  = left(B.RetVal,charindex('=',B.RetVal+'=')-1)
      ,Value = right(B.RetVal,len(B.RetVal)-charindex('=',B.RetVal+'=')-1)
 From  YourTable A 
 Cross Apply (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace((Select replace(A.Parameters,'|','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                Cross Apply x.nodes('x') AS B(i)
             ) B
 -- YOUR OPTIONAL WHERE STATEMENT HERE  For example: Where A.ID=1

Returns

enter image description here

Upvotes: 1

gemmo
gemmo

Reputation: 1456

All I can see is that you chose one column which results in a lot of text in that column. You can export this to excel and use the "Text to Column" function, and have PIPE | as a delimiter, and the EQUAL = sign. This will result in a separate column for each | and = sign.

Upvotes: 0

Related Questions