how to get value from string

Question

how to get code and sub code from query sql like this from column A with

format :

[very long name | code | subcode]

for the example

[Pear | S01 | 10]
[Pineapple | S01 | 9]

PS : there is no square brackets but the the pipe delimiters is real

Is `[Pear | S01 | 10]` literally one field, and you need to parse it? — LittleBobbyTables - Au Revoir, Feb 05 '13 at 16:35
@YanSusanto: Does the line actually contain the square brackets and the pipe delimiters, or are you just adding them for illustration? — Cᴏʀʏ, Feb 05 '13 at 16:37
See http://stackoverflow.com/questions/7983029/t-sql-substring, http://stackoverflow.com/questions/10921400/t-sql-substring-separating-first-and-last-name. Also see: http://stackoverflow.com/questions/2647/split-string-in-sql and http://stackoverflow.com/questions/10914576/tsql-split-string — LittleBobbyTables - Au Revoir, Feb 05 '13 at 16:44

score 3 · Accepted Answer · answered Feb 05 '13 at 16:43

Try this; DEMO - UPDATED WITH A TABLE

--declare @s varchar(50)='[Pineapple | S01 | 9]'

select substring(yourColumn,ind1 + 1,ind2-(ind1 +1)) code, 
       substring(yourColumn,ind2 + 1,len(yourColumn)-(ind2 + 1)) subcode
FROM ( 
  SELECT yourColumn, charindex('|',yourColumn,1) ind1, 
         charindex('|',yourColumn,charindex('|',yourColumn,1)+1) ind2 
  FROM yourTable
) X

score 1 · Answer 2 · answered Feb 05 '13 at 16:52

Assuming your codes are always three letters, you could use some built-in functions to help you out:

WITH Data(Sample) AS
(
    SELECT 'Pear | S01 | 10' UNION
    SELECT 'Pineapple | S01 | 9'
)
SELECT
    SUBSTRING([Sample], CHARINDEX(' | ', [Sample], 1) + 3, 3) AS Code,
    REVERSE(SUBSTRING(REVERSE([Sample]), 1, 
        CHARINDEX(' | ', REVERSE([Sample]), 1) - 1)) AS SubCode
FROM
    data

how to get value from string

2 Answers2