How to search for last two characters and then remove them?-CodePudding

How would you go about searching for ND or RD in address like 200 162nd Street and removing it and having the output be : 200 162 Street

I know I need to do something like select RIGHT(address, 2);

The hard part is searching for the ND or RD and removing it after there is an unknown amount of characters before and after it, ie it can be Street, Boulevard, Avenue, etc

Any help would be greatly appreciated.

Thank you.

select RIGHT(address, 2);

CodePudding user response：

Perhaps the following is something you can try, using a combination of patindex and stuff.

with t as (
  select * from (values('200 162nd Street'),('23rd Street'),('The strand not this'))x(s)
)
select s as SourceString, IsNull(Stuff(s, NullIf(p,0)   1, 2, ''), s) Removed
from t
cross apply (values(PatIndex('%[0-9][r,n][d][ ]%', s)))p(p);

CodePudding user response：

SELECT 
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE(
                                        REPLACE(address, '1st', '1'), 
                                        '2nd', '2'
                                    ), '3rd', '3'
                                ), '4th', '4'
                            ), '5th', '5'
                        ), '6th', '6'
                    ), '7th', '7'
                ), '8th', '8'
            ), '9th', '9'
        ), '0th', '0'
    ) AS "address"
FROM ..

Not a beauty to be honest.

CodePudding user response：

Combination of approaches shown thus far. The CASE expression assumes one of these 2-letter strings only occurs once.

SELECT addr
    ,CASE WHEN pat != 0
            THEN STUFF(addr, pat   1, 2, '')
        ELSE addr2
    END AS AddrProcessed
FROM (
    SELECT '200 160th Street' UNION ALL
    SELECT '200 161st Street' UNION ALL
    SELECT '200 162nd Street' UNION ALL
    SELECT '200 163rd Street' UNION ALL
    SELECT '200 164th Street' UNION ALL
    SELECT '200 165th Street' UNION ALL
    SELECT '200 166th Street' UNION ALL
    SELECT '200 167th Street' UNION ALL
    SELECT '200 168th Street' UNION ALL
    SELECT '200 169th Blvd'
    )t(addr)
CROSS APPLY (
    SELECT REPLACE(REPLACE(REPLACE(addr, '1st', '1'),'2nd','2'),'3rd','3')
    ,PATINDEX('%[0,4-9]th%', addr)
    )calc(addr2, pat)

CodePudding user response：

You should use a substring or a find function in addition to your replace method. You didn't post what language you are attempting this in. In SQL you could just use the REPLACE function

For the string 'SQL Tutorial' if you want to replace all T's with M's then, SELECT REPLACE('SQL Tutorial', 'T', 'M');

Results in the string becoming: SQL MuMorial

https://www.w3schools.com/sql/func_sqlserver_replace.asp