Separate a column in several SQL Server

Question

Separate a column in several SQL Server

Navigation

#1 by (2 votes)

3

Does anyone know how to divide a field into several columns?

I have an sp, which gives me the following information.

I would like to divide the Week field into several columns

SemanaAño   Semana                  FechaInicio FechaFin    RangoFechas
58       Febrero 2017 - Semana 2    2017-02-05  2017-02-11  2017-02-05*2017-02-11
57       Febrero 2017 - Semana 1    2017-01-29  2017-02-04  2017-01-29*2017-02-04

For example that I would stay this way:

SemanaAño   Mes     Año  Semana    No_Semana FechaInicio    FechaFin    RangoFechas
58          Febrero 2017 Semana 1      1     2017-02-05     2017-02-11 2017-02-11   2017-02-05*2017-02-11

sql sql-server

asked by Ric_hc 16.02.2017 в 16:17

source

1 answer

download file from url with ajax and asp.net mvc 5 Redirect access with sessions

score 2 · Accepted Answer

The function you are looking for is Substring()

SELECT RTRIM(SUBSTRING(Semana,1,CHARINDEX(' ', Semana)-1)) AS Mes, 
RTRIM(SUBSTRING(Semana,CHARINDEX(' ', Semana) + 1, (CHARINDEX('-', Semana) -1)-(CHARINDEX(' ', Semana) + 1)) ) AS Año, 
RTRIM(SUBSTRING(Semana, CHARINDEX('-', Semana)+1, LEN(@Semana))) AS Semana
FROM 
    table_name;

Explanation

RTRIM - > makes trim to remove spaces at the beginning or end of the String

SUBSTRING - > Take a part of "Febrero 2017 - Semana 2" happening where you start to take and how many characters.

inside substring we use CHARINDEX that returns the position of a char . I use it to tell substring from where to where it cuts.

With these functions in the select I am creating the Month, Year and Week columns from the week of your table.

Note: Try it on fiddle: link