AndrewSQLDBA (3/12/2013)
Hello EveryoneI am working on some old data that should not be store this way, but it is.
My data is two values with pipe separators.
10.0||14.5
or
2||34
or
7.1||19
or
4||11.7
I need to query this column and get each value separately. I have no idea how to go about this. There are always double pipe in the middle. But as you can see, there may or may not be decimal values in each. I think that is what is throwing me off. When perhaps, it really may not matter about the actual values.
I appreciate any and all assistance, suggestions and comments
Andrew SQLDBA
For a one-off update, just use charindex
SELECT LEFT(value,CHARINDEX('||',value)-1) as Part1,STUFF(value,1,CHARINDEX('||',value)+1,'') as Part2
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);