Clever Geek Handbook

Retrieve a single value from a column containing multiple bounded values

How do I get the value from the sixth field in the next column? I am trying to get field 333:

ORGPATHTXT
2123/2322/12323/111/222/333/3822

I believe I need to use select substring

, but I'm not sure how to format the request

+3


source to share


6 answers


Assuming SQL Server

The easiest way I can think of is to create a Split function that breaks with "/" and you retrieve the sixth item as shown below



declare @text varchar(50) = '2123/2322/12323/111/222/333/3822'

select txt_value from fn_ParseText2Table(@text, '/') t where t.Position = 6

I have used a function in this url . See how it works in SQLFiddle

+2


source


Try this - for string variable

or wrap in function

for use with select query ( Sql-Demo )



Declare @s varchar(50)='2123/2322/12323/111/222/333/3822'

Select @s = right(@s,len(@s)- case charindex('/',@s,1) when 0 then len(@s) 
                              else charindex('/',@s,1) end)
From ( values (1),(2),(3),(4),(5)) As t(num)

Select case when charindex('/',@s,1)>0 then left(@s,charindex('/',@s,1)-1) 
            else @s end

--Results
333
+2


source


I would like to suggest a solution that uses CROSS APPLY

to split any separable string in MSSQL and ROW_NUMBER()

to return the 6th item. This assumes you have a table with ORGPATHTXT as a field (it can be easily converted to work without a table):

SELECT ORGPATHTXT
FROM (
  SELECT 
    Split.a.value('.', 'VARCHAR(100)') AS ORGPATHTXT,
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT 1)) RN
  FROM  
    (SELECT ID, CAST ('<M>' + REPLACE(ORGPATHTXT, '/', '</M><M>') + '</M>' AS XML) AS String  
     FROM  MyTable
  ) AS A 
  CROSS APPLY String.nodes ('/M') AS Split(a)
  ) t 
WHERE t.RN = 6;

Here are some Fiddle examples to go along with it.

Good luck.

+1


source


For sql, you can use

declare @string varchar(65) = '2123/2322/12323/111/222/333/3822'

select substring(string,25,27) from table_name
+1


source


If you are using MySQL you can use:

select substring_index(orgpathtxt, '/', 6)

Let me say that this is less convenient in most other databases.

0


source


Also you can use option with dynamic control function sys.dm_fts_parser

DECLARE @s nvarchar(50) = '2123/2322/12323/111/222/333/3822' 

SELECT display_term
FROM sys.dm_fts_parser('"'+ @s + '"', 1033, NULL, 0) 
WHERE display_term NOT LIKE 'nn%' AND occurrence = 6
0


source






More articles:


All Articles