how to fetch the pin code in address using sql query

Question

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

Suraj Kumar · Answer

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

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-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

Deepak Rawat · Answer

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: 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)) This formula will consider that the pin code is always represented by six digits and is separated by a hyphen ("-"). After entering the formula, the cell will display "201301," which is the pin code from the address. 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.

Vishal Yelve · Answer

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