CODEWITHSUNDEEP
sql-server-2008t-sql
patwary
patwary

Reputation: 153

SQL server triming

I am trying to format the licenseenum column in my table by removing everything starting the before space and also I would like to remove any character in licenseenum column starting after '-' including '-'

For example:
current data in Licenseenum GA 350-0 What I'm trying to get 350

Here is my code

select Licenseenum, SUBSTRING (Licenseenum, 4, LEN(Licenseenum)-1)
from licensee

this results 350-0

How would I remove -0 from the results?

Thanks for the help

Upvotes: 2

Views: 117

Answers (2)

Praveen ND
Praveen ND

Reputation: 560

Please try the below code. Its working fine in SQL Server 2012.

DECLARE @Licenseenum  varchar(max)
SET @Licenseenum  ='GA 350-0'

DECLARE @TempLicense VARCHAR(100)
DECLARE @License VARCHAR(100)
IF (len(@Licenseenum ) - len(replace(@Licenseenum ,' ',''))>=1)
    SET @TempLicense = (SELECT REVERSE(LEFT(REVERSE(@Licenseenum ),CHARINDEX(' ', REVERSE(@Licenseenum ), 1) - 1)))
ELSE
    SET @TempLicense = @Licenseenum 

SELECT  @License = (SELECT LEFT(@TempLicense,LEN(@TempLicense) - charindex('-',reverse(@TempLicense),1)))
SELECT @License AS Licenseenum

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Try it like this

DECLARE @YourString VARCHAR(100)='GA 350-0';

SELECT SUBSTRING(@YourString,CHARINDEX(' ',@YourString,1),CHARINDEX('-',@YourString,1)-CHARINDEX(' ',@YourString,1));

UPDATE 1

This is quite the same, but better to read

DECLARE @YourString VARCHAR(100)='GA 350-0';

WITH Positions AS
(
    SELECT CHARINDEX(' ',@YourString,1) AS posBlank
          ,CHARINDEX('-',@YourString,1) AS posMinus  
)
SELECT SUBSTRING(@YourString,posBlank,posMinus-posBlank)
FROM Positions;

UPDATE 2 Avoid the leading blank...

My logic needs small correction in order to cut the blank before the number:

SELECT SUBSTRING(@YourString,posBlank+1,posMinus-posBlank-1)

Would be the same with the first example...

Upvotes: 2

Related Questions