CODEWITHSUNDEEP
sql-server
Kentgi
Kentgi

Reputation: 318

how to get 2 words with SUBSTRING in SQL SERVER

i have a column containing like this

Term
------- 
Semester Genap 2016/2017 
Semester Ganjil 2016/2017

i want to get

Term
------- 
Semester Genap 
Semester Ganjil

how to do it with SUBSTRING?

Upvotes: 1

Views: 4076

Answers (6)

TriV
TriV

Reputation: 5148

If your term perhaps includes old data like 199x/199x then you could use PATINDEX to find the first number in your string.

DECLARE @Term varchar(max) = 'Semester Genap 2016/2017'

SELECT RTRIM(SUBSTRING(@Term, 1, PATINDEX('%[0-9]%', @Term +'0') - 1))

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

And one more approach, using a string-split approach via XML:

DECLARE @tbl TABLE(ID INT IDENTITY,Term VARCHAR(100));
INSERT INTO @tbl VALUES
 ('Semester Genap 2016/2017')
,('Semester Ganjil 2016/2017');

WITH Casted AS (
                SELECT ID
                      ,CAST('<x>' + REPLACE((SELECT Term AS [*] FOR XML PATH('')),' ','</x><x>') + '</x>' AS XML) AS TheXML
                FROM @tbl
               )
SELECT ID 
      ,TheXML.value(N'(x/text())[1]','nvarchar(max)') AS Part1
      ,TheXML.value(N'(x/text())[2]','nvarchar(max)') AS Part2
      ,TheXML.value(N'(x/text())[3]','nvarchar(max)') AS Part3
FROM Casted;

The result

ID  Part1       Part2   Part3
1   Semester    Genap   2016/2017
2   Semester    Ganjil  2016/2017

Upvotes: 2

Nikhil Mathew
Nikhil Mathew

Reputation: 56

Another variant for the problem. 

SELECT SUBSTRING('Semester Genap 2016/2017',1,LEN('Semester Genap 2016/2017') - CHARINDEX(' ',REVERSE('Semester Genap 2016/2017')))

Upvotes: 0

schroedingersKat
schroedingersKat

Reputation: 189

Something along the lines of SUBSTRING(@statement,0,CHARINDEX(@statement,' ',CHARINDEX(@statement,' ')+1)). I don't have any way to test it right now, but it should get you in right direction.

  1. CHARINDEX(@statement,' ') - start by finding first space (this ends first word
  2. CHARINDEX(@statement,' ',CHARINDEX(@statement,' ')+1) - next find the next space, or rather find first space starting from right after first space (this marks end of second word)
  3. SUBSTRING(@statement,0,CHARINDEX(@statement,' ',CHARINDEX(@statement,' ')+1)) - get the substring starting from the beginning right to the second space.

Upvotes: 0

BeanFrog
BeanFrog

Reputation: 2315

You can use charindex to find the spaces, and use that to get the first 2 words:

declare @t nvarchar(50);
set @t = 'Semester Genap 2016/2017';

select @t, CHARINDEX(' ', @t), SUBSTRING(@t, 0, charindex(' ', @t, charindex(' ',@t) + 1))

Upvotes: 0

GandRalph
GandRalph

Reputation: 635

Assuming you always want everything to the left of a date, and the date starts with a "2", then this will work (Horrible, but works):

Just replace variable (used for testing only) with your column:

declare @String varchar(50) = 'Semester Genap 2016/2017'

select SUBSTRING(@String,0,len(@String)-(len(@String)-CHARINDEX('2',@String,0))-1)

Produces: 'Semester Genap'

Upvotes: 0

Related Questions