Lutch Lutch - 10 months ago 40
SQL Question

Extract data from square brackets in SQL

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download