Altirisguru Altirisguru - 5 months ago 10
SQL Question

sql get get first character, trim till period, then remaining characters

I have a column of names and I need to select the first character till a period then get the remaining characters.

Example

Source Column name Desired output

bob.smith bsmith
mary.alice malice
bill.gates bgates

Answer

CHARINDEX will allow you to search for the location of the period, you can then use SUBSTRING to return the data you need.

Sample Data;

CREATE TABLE #TempData (FieldName varchar(10))
INSERT INTO #TempData (FieldName)
VALUES
('bob.smith')
,('mary.alice')
,('bill.gates')

Query

SELECT 
FieldName
,SUBSTRING(FieldName,1,1) + SUBSTRING(FieldName,CHARINDEX('.',FieldName)+1,LEN(FieldName)-CHARINDEX('.',FieldName)) Result
FROM #TempData

Result

FieldName   Result
bob.smith   bsmith
mary.alice  malice
bill.gates  bgates
Comments