how to fetch the pin code in address using sql query

Question

Address is house no-42sector-39gautam budh nagar noida- 201301 land line-N/A how to fetch 201301 in seprate colum name pin code.

Suraj Kumar · Answer

Hello Vinay You can write your query as below DECLARE @str varchar(max) = 'house no-42sector-39gautam budh nagar noida-201301 land line-N/A' SELECT SUBSTRING(@str(SELECT PATINDEX('%[0-9][0-9][0-9][0-9][0-9][0-9]%'@str))6) AS PinCode The output will be 201301 Thanks

Ajeesh · Answer

Code Provided by Suraj is correct However I added an additional check condition so that when the zip is not 6 digit number you will get a message like 'Pincode no valid' like below DECLARE @str varchar ( max ) = 'house no-42sector-39gautam budh nagar noida-201111 land line-N/A' SELECT CASE WHEN PATINDEX( '%[0-9][0-9][0-9][0-9][0-9][0-9]%'  @str) > 0 THEN SUBSTRING (@str PATINDEX( '%[0-9][0-9][0-9][0-9][0-9][0-9]%'  @str) 6) ELSE 'Pincode Not valid' End Sample Demo here :http://sqlfiddle.com/#!6/9eecb7db59d16c80417c72d1e1f4fbf1/18880