Separate strings in SQL

Question

Separate strings in SQL

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I have a problem and I hope you can help me.

My problem is that I have to include in an SQL view (that I will later send in visual studio 2013) the fields "city" and "state" but in the source table the data comes in the same column and abbreviated. So I require not only to separate the string, but also to put the state in another column and show the non-abbreviated state, that is, to put "city" in one column and "state" in another.

For example I have in my current table:

CIUDAD

'MONTERREY NL' 

'DISTRITO FEDERAL DF'

'SAN NICOLAS GARZA GARCIA NL'

And I need:

CIUDAD|ESTADO

'MONTERREY'|'NUEVO LEON'

'DISTRITO FEDERAL'|'DISTRITO FEDERAL'

'SAN NICOLAS GARZA GARCIA'|'NUEVO LEON'

I already tried with a substring and charindex and nothing. What has caused me more conflict is that the length of the cities and states is variable so I can not extract a certain number of characters and they do not have a delimiter like comma, period or something similar on which I can base to extract and validate the state.

sql visual-studio-2013

asked by Gloria López 19.01.2018 в 16:52

source

2 answers

Angular 2: Array declared with const is modified How to calculate the average of the differences between timestamps in php?

score 1 · Answer 1

We do not have at hand the LastCharIndex () function that would have been propitious to obtain the last character of white space of the chain, however if it is possible to recover the chain in a reverse order and take the position index of the first character of white space and occupy it as an argument to the function RIGHT (), for example:

DECLARE @Ciudades table (Ciudad varchar(300));
INSERT INTO @Ciudades VALUES
('MONTERREY NL'), ('DISTRITO FEDERAL DF'), ('SAN NICOLAS GARZA GARCIA NL');

SELECT LEFT(c.Ciudad, LEN(c.Ciudad) - LEN(e.Codigo) - 1) AS Ciudad, e.Estado
FROM 
    @Ciudades c
    CROSS APPLY(SELECT RIGHT(c.Ciudad, CHARINDEX(' ', REVERSE(c.Ciudad)) - 1)) t(Estado)
    INNER JOIN (
       VALUES ('NL', 'NUEVO LEON'), ('DF', 'DISTRITO FEDERAL')
       ) e (Codigo, Estado) ON t.Estado = e.Codigo
GO

score 0 · Answer 2

You can create a function to get the value you need from the chain, something like this:

    CREATE FUNCTION dbo.ObtenerEdo(@Cadena VARCHAR(100)) 
    RETURNS  VARCHAR(10)
    AS
    BEGIN

    DECLARE @Valor      VARCHAR(50)
    DECLARE @Tabla      TABLE (Num INT IDENTITY(1,1), Valor VARCHAR(10))
    DECLARE @Edo        VARCHAR(10)

    WHILE LEN(@Cadena) > 0
    BEGIN
        IF CHARINDEX(' ',@Cadena) > 0
        BEGIN
            SET @Valor  = LEFT(@Cadena,CHARINDEX(' ',@Cadena)-1)
            SET @Cadena = RIGHT(@Cadena,LEN(@Cadena) - CHARINDEX(' ',@Cadena))

            INSERT INTO @Tabla (Valor)
            SELECT @Valor
        END
        ELSE
        BEGIN
            SET @Valor  = @Cadena
            SET @Cadena = ''

            INSERT INTO @Tabla (Valor)
            SELECT @Valor
       END
    END

    SELECT  @Edo  = Valor FROM @Tabla   WHERE  Num  =   (SELECT MAX(Num) FROM @Tabla)

    RETURN  @Edo
    END

and then use it in your query to replace the state with emptiness, it would be something like this:

    SELECT REPLACE(Ciudad, dbo.ObtenerEdo(Ciudad), ''), dbo.ObtenerEdo(Ciudad) FROM TABLA

So you get both fields, and you only need a join to get the value of the state of your table.

I hope it serves you. Greetings.