CODEWITHSUNDEEP
sqloracle-databasetoad
user2990687
user2990687

Reputation: 1

Need a query to find a string in address column

I want to select first 4 letters of the address string ignoring the numbers or P.O. box.

For example, I have a database column "address" in "customers" table.

51 church st

In a query, I only want "chur" ignoring the numbers. It can be any number. I am not interested in number. Also, I don't want this for just one record. I want this to happen for every record So for example I have these records:

51 church st
6178 fookeral ave
597537 state ct

In my 1 query i want results look like this

Chur
Fook
Stat

How can I get this in one query?

Upvotes: 0

Views: 1450

Answers (2)

jpw
jpw

Reputation: 44911

As an alternative to CHARINDEXyou could use PATINDEX and have it match any character in the a-zrange:

SELECT SUBSTRING(address, PATINDEX('%[a-z]%', address), 4) AS FirstFour
FROM customers

This would give the correct result if you have any address like 51 32 church st, that is with a number followed by a space followed by another number.

This solution assumes you use Microsoft SQL Server.

Edit: added a solution for Oracle (tested with 11g R2).

In Oracle you can use regular expressions and the REGEXP_SUBSTR function like this:

SELECT
  REGEXP_SUBSTR("Address",'[[:alpha:]]{4}') "FourChars",
  INITCAP(REGEXP_SUBSTR("Address",'[[:alpha:]]{4}')) "FourCharsInitCap"
FROM customers

This returns:

FOURCHARS   FOURCHARSINITCAP
chur        Chur
fook        Fook
stat        Stat

Upvotes: 1

David
David

Reputation: 34573

The CHARINDEX function lets you search for a string within another string. Use this to find the location of the space character, then take the 4 following characters.

Here's an example: 

SELECT SUBSTRING([Address], CHARINDEX(' ', [Address]) + 1, 4)
FROM [Your_Table]

Upvotes: 1

Related Questions