Extract data from square brackets in SQL

Question

while doing an sql query, i have a field Gift_Number which give me the below data

Gift certificat (-) [2989153053216]
Sql Query :

           SELECT SUBSTRING(H.F1056, patindex('%[^0]%',H.F1056), 10) AS Shop_Number
                  , '000' AS Cashier_Code
                  , H.F1057 AS Terminal_number
                  , REPLACE(CONVERT(VARCHAR(10), H.F254, 103), '/', '') AS Todays_Date
                  , STIME AS Ticket_Time
                  , H.F1032 AS Ticket_Number
                  , K.F1063 AS Mode_Of_Payment
                  , K.F02 AS Mode_Of_Payment_Desc
                  , CASE WHEN J.F1063 = 117 
                         THEN J.F02 
                         ELSE '' END AS Gift_Number
                  , CASE WHEN I.F02 = 'TOTAL' 
                         THEN I.F65 
                         ELSE 0 END AS Total_Ticket
             FROM [READEJ_H] H
  LEFT OUTER JOIN [dbo].[READEJ_I] I 
               ON (H.F1101 = I.F1101 AND I.F02 = 'TOTAL')
  LEFT OUTER JOIN [dbo].[READEJ_I] K 
               ON (H.F1101=K.F1101 AND K.F1063 BETWEEN 100 AND 199)
  LEFT OUTER JOIN [dbo].[READEJ_I] J 
               ON (H.F1101=J.F1101 AND J.F1063 = 117)
            WHERE I.F65  <> CONVERT(DOUBLE PRECISION,0)

How can i extract only the information between the square brackets in the sql select statement.The output should be as below :

    2989153053216

Also please do some research. This question is probably a duplicate. — Zohar Peled
– Zohar Peled, Commented Aug 17, 2017 at 6:43
@ZoharPeled I have tried other research but to no avail. RealCheeseLord I have included my query — Lutch
– Lutch, Commented Aug 17, 2017 at 6:50

Zohar Peled · Accepted Answer · 2017-08-17 06:48:49Z

2

Probably the simplest way is to use charindex and substring:

DECLARE @S varchar(100) = 'Gift certificat (-) [2989153053216]'

SELECT SUBSTRING(@S, CHARINDEX('[', @S)+1, CHARINDEX(']', @S) - CHARINDEX('[', @S)-1)

Result: 2989153053216

However, you should be aware that this will raise an error if the string does not contain the delimiters, or if the ] delimiter comes before the [ delimiter.

answered Aug 17, 2017 at 6:48

Zohar Peled

82.9k10 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lutch Over a year ago

It works, the data will always be in the square brakets

Zohar Peled Over a year ago

Glad to help :-)

Collectives™ on Stack Overflow

Extract data from square brackets in SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related