how to fetch the pin code in address using sql query

Try this

SELECT Address
      ,SUBSTRING(Address, PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', Address),6) AS ZipCode
FROM Table

To fetch the pin code "201301" from the given address and place it in a separate column named "Pin Code," you can use string manipulation or text extraction functions in spreadsheet software like Microsoft Excel or Google Sheets. Here's a step-by-step guide:

Assuming the address "house no-42, sector-39, gautam budh nagar, noida-201301" is in cell A1:

1. In a separate cell, use the following formula to extract the pin code:

=RIGHT(A1, 6)

Google Sheets:

=RIGHT(A1, LEN(A1)-FIND("-", A1, FIND("-", A1)+1))

1. This formula will consider that the pin code is always represented by six digits and is separated by a hyphen ("-").

2. After entering the formula, the cell will display "201301," which is the pin code from the address.

3. If you want to place the extracted pin code in a separate column (e.g., column B), copy the formula cell and paste it in the desired column for all the addresses you have.

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-42,sector-39,gautam 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

Hello Vinay,

You can write your query as below

DECLARE @str varchar(max) = 'house no-42,sector-39,gautam 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