How do I remove unknown characters in a string?

Question

I would like to delete parts of an string.

We have a Table: Locations

mk-MK=New York; sq-AL=Nej York; en-US=New York

mk-MK=London; sq-AL=London; en-US=London

mk-MK=Paris; sq-AL=Paris; en-US=Paris

I Want to remove everything and keep only sq-AL=LocationName.

I want the result to be:

sq-AL=Nej York;
sq-AL=London;

what is name of column for string : `mk-MK=New York; sq-AL=Nej York; en-US=Tetovo` — Vikrant, Apr 26 '15 at 10:57
Consider using regular expressions (REGEX) in your query, example: [How to regex in a sql query](http://stackoverflow.com/questions/18780194/how-to-regex-in-a-sql-query). — nicolaus-hee, Apr 26 '15 at 11:10
I dont have 100% understanding of RegExp, any suggestion what path I can write? — Arif, Apr 26 '15 at 11:32

Zohar Peled · Accepted Answer · 2015-04-26T12:17:38.037

This is yet another example of the importance of normalized databases.
In a normalized database you would have a table with 2 columns, one for the culture (sq-Al, en-US etc`) and one for the value. I would go a step further and have the cultures in a lookup table.

However, since this is not the case you have to use string manipulations to get the value of a specific culture. you can use SUBSTRING and CHARINDEX to find the specific pattern you want.
This will work in any of the cases represented by the sample data I've listed.

-- Create the table and insert sample data
CREATE TABLE Location ([Name] varchar(100))
INSERT INTO Location ([Name]) VALUES 
('en-US=Huston; mk-MK=Huston; sq-AL=Huston;'), -- end of the row, with the ending ';'.
('en-US=New York; mk-MK=New York; sq-AL=Nej York'), -- end of the row, without the ending ';'.
('mk-MK=London; sq-AL=London; en-US=London'),  -- middle of the row
('sq-AL=Paris; en-US=Paris; mk-MK=Paris') -- begining of the row



SELECT  SUBSTRING(Name, 
        CHARINDEX('sq-AL=', Name), -- index of 'sq-AL='
        CASE WHEN CHARINDEX(';', Name, CHARINDEX('sq-AL=', Name)) > 0 THEN -- If there is a ';' after 'sq-AL='.
            CHARINDEX(';', Name, CHARINDEX('sq-AL=', Name)) -- index of the first ';' after 'sq-AL=' 
            - CHARINDEX('sq-AL=', Name)  -- index of the first ';' - the index of 'sq-AL=' will give you the length for `Nej York`
        ELSE 
            LEN(Name) 
        END
    ) + ';'
FROM Location

-- Cleanup
DROP Table Location

Glad to help. you should really reconsider your table design. — Zohar Peled, Apr 28 '15 at 07:20

Vikrant · Answer 2 · 2015-04-26T11:33:10.413

0

You can use CHARINDEX function. I've tried same with a variable as,

declare @locations varchar(100) = 'mk-MK=New York; sq-AL=Nej York; en-US=New York'

select LEFT(
          RIGHT(
                 @locations, LEN(@locations)-CHARINDEX(';',@locations) 
                 --output here :  sq-AL=Nej York; en-US=New York
               )
             ,CHARINDEX(';',@locations)
           )  + ';'
--Final Output :  sq-AL=Nej York;

In your case: Query will be as,

select LEFT(
          RIGHT(
                 Name, LEN(Name)-CHARINDEX(';',Name) 
                 --output here :  sq-AL=Nej York; en-US=New York
               )
             ,CHARINDEX(';',Name)
           )  + ';' 
FROM Locations

edited Apr 26 '15 at 11:33

answered Apr 26 '15 at 11:28

Vikrant

4,920
17
48
72

This will only work assuming that `sq-AL=% `will always be only the second part of the string. a string like `en-US=New York; mk-MK=New York; sq-AL=Nej York;` will return `mk-MK=New York`. – Zohar Peled Apr 26 '15 at 11:32
@Arif, try out the solution & let me know if it's helpful! – Vikrant Apr 26 '15 at 11:34

How do I remove unknown characters in a string?

2 Answers2