Selecting Substring

Question

I have got data like this in a field:

"Shop 7 Ground Level 145 Upper Heidelberg Road, IVANHOE"
"Shop 1 Ground Level 1 Flintoff Street, GREENSBOROUGH"

And I want to have Building Number and Street address in different columns, like below

145 | Upper Heidelberg Road
 1  | Flintoff Street

What is your underlying SQL database (e.g. MySQL, SQL Server, Oracle, PostgreSQL, etc.,)? — James, Dec 04 '19 at 04:56
This is highly impossible unless it follows some standard patterns like starts at exact position or delimited by some chars, etc — Thangadurai, Dec 04 '19 at 04:59
Check your table structure because it is not possible to form such pattern. You need to add column like street-name or street-number in your table.That is the correct way — Kedar Limaye, Dec 04 '19 at 05:01
yes i have made Street Name , Street Address and want to populate this columns — MHI, Dec 04 '19 at 05:04
anything after "level" is street number and then after space its Street Address — MHI, Dec 04 '19 at 05:07
https://stackoverflow.com/questions/16413/parse-usable-street-address-city-state-zip-from-a-string — Ke Zhu, Dec 04 '19 at 06:02

mkRabbani · Answer 1 · 2019-12-04T06:33:33.487

You can try this below logic-

Consider the first word after "Level " is building number. After building number, string till next comma(",") is street address

WITH your_table(address)
AS
(
    SELECT 'Shop 7 Ground Level 145 Upper Heidelberg Road, IVANHOE' UNION ALL
    SELECT 'Shop 1 Ground Level 1 Flintoff Street, GREENSBOROUGH'
)

SELECT 
LEFT(
    RIGHT(address,LEN(address)-CHARINDEX('Level',address,0)-5),
    CHARINDEX(' ',RIGHT(address,LEN(address)-CHARINDEX('Level',address,0)-5),0)
) Building_number,
LEFT(
    RIGHT(
        RIGHT(address,LEN(address)-CHARINDEX('Level',address,0)-5),
        LEN(RIGHT(address,LEN(address)-CHARINDEX('Level',address,0)-5))-CHARINDEX(' ',RIGHT(address,LEN(address)-CHARINDEX('Level',address,0)-5),0)
    ),
    CHARINDEX(
        ',',
        RIGHT(
            RIGHT(address,LEN(address)-CHARINDEX('Level',address,0)-5),
            LEN(RIGHT(address,LEN(address)-CHARINDEX('Level',address,0)-5))-CHARINDEX(' ',RIGHT(address,LEN(address)-CHARINDEX('Level',address,0)-5),0)
        )
        ,
        0
    )-1
) Street_address
FROM your_table

In case if there are possibilities of not having a Comma(,) after the building number, You can use this following logic-

SELECT 
LEFT(
    RIGHT(address,LEN(address)-CHARINDEX('Level',address,0)-5),
    CHARINDEX(' ',RIGHT(address,LEN(address)-CHARINDEX('Level',address,0)-5),0)
) Building_number,

LEFT(
    RIGHT(
        RIGHT(address,LEN(address)-CHARINDEX('Level',address,0)-5),
        LEN(RIGHT(address,LEN(address)-CHARINDEX('Level',address,0)-5))-CHARINDEX(' ',RIGHT(address,LEN(address)-CHARINDEX('Level',address,0)-5),0)
    ),
    IIF(            
        CHARINDEX
        (
            ',',
            RIGHT(
                RIGHT(address,LEN(address)-CHARINDEX('Level',address,0)-5),
                LEN(RIGHT(address,LEN(address)-CHARINDEX('Level',address,0)-5))-CHARINDEX(' ',RIGHT(address,LEN(address)-CHARINDEX('Level',address,0)-5),0)
            )
            ,
            0
        ) = 0 , 
        LEN(
            RIGHT(
                RIGHT(address,LEN(address)-CHARINDEX('Level',address,0)-5),
                LEN(RIGHT(address,LEN(address)-CHARINDEX('Level',address,0)-5))-CHARINDEX(' ',RIGHT(address,LEN(address)-CHARINDEX('Level',address,0)-5),0)
            )
        )
        ,
        CHARINDEX
        (
            ',',
            RIGHT(
                RIGHT(address,LEN(address)-CHARINDEX('Level',address,0)-5),
                LEN(RIGHT(address,LEN(address)-CHARINDEX('Level',address,0)-5))-CHARINDEX(' ',RIGHT(address,LEN(address)-CHARINDEX('Level',address,0)-5),0)
            )
            ,
            0
        )-1
    )           
) Street_address
FROM your_table

Output is-

Building_number Street_address
145             Upper Heidelberg Road
1               Flintoff Street

Vignesh Kumar A · Answer 2 · 2019-12-04T07:38:19.747

Use CHARINDEX to get the specific word and after use Function` to split the string and number.

DB FIDDLE

SELECT dbo.GetNumericValue('145 Upper Heidelberg Road') [Building Number], REPLACE('145 Upper Heidelberg Road',dbo.GetNumericValue('145 Upper Heidelberg Road'),'') [Street Name] 
FROM
(
   SELECT SUBSTRING(Data, CHARINDEX('Ground Level', Data) + LEN('Ground Level') + 1, CHARINDEX(', ',Data) - (CHARINDEX('Ground Level', Data) + 1 + LEN('Ground Level'))) Address
   FROM TAB
)X

Function

CREATE FUNCTION dbo.GetNumericValue  (@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS  
BEGIN  
    DECLARE @intAlpha INT  
    SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)  
    BEGIN  
        WHILE @intAlpha > 0  
        BEGIN  
            SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )  
            SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )  
        END  
    END  
    RETURN ISNULL(@strAlphaNumeric,0)  
END;

Output

Building Number Street Name
145             Upper Heidelberg Road
1               Flintoff Street

score 0 · Answer 3 · answered Dec 11 '19 at 05:52

declare @str varchar(250)
declare @start int
declare @end int
declare @subStr varchar(250)
declare @buildingNumber varchar(50)
declare @street varchar(150)

set @str = ('Shop 1 Ground Level 1 Flintoff Street, GREENSBOROUGH')

set @start = (ChARINDEX('Level', @str)) + 5
set @end = (ChARINDEX(',', @str))

set @subStr = SUBSTRING(@str, @start, (@end - @start)) 
set @subStr = LTRIM(@subStr)
set @subStr = RTRIM(@subStr)

--select @subStr

set @buildingNumber = SUBSTRING(@subStr, (ChARINDEX(' ', @subStr))-1, (ChARINDEX(' ', @subStr))) 

---If you want to have space between buidling number and street address comment following two lines
set @buildingNumber = LTRIM(@buildingNumber)
set @buildingNumber = RTRIM(@buildingNumber)

--select @buildingNumber

set @street = SUBSTRING(@subStr, (ChARINDEX(' ', @subStr)), (Len(@subStr))) 

---If you want to have space between buidling number and street address comment following two lines
set @street = LTRIM(@street)
set @street = RTRIM(@street)

--select @street

select @buildingNumber + '|' + @street

Selecting Substring

3 Answers3