2

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

Iłya Bursov
  • 23,342
  • 4
  • 33
  • 57
patwary
  • 153
  • 2
  • 12
  • Possible duplicate of [Get everything after and before certain character in SQL Server](http://stackoverflow.com/questions/11010453/get-everything-after-and-before-certain-character-in-sql-server) – Sun May 24 '16 at 21:44

2 Answers2

2

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...

Shnugo
  • 66,100
  • 9
  • 53
  • 114
1

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
Praveen ND
  • 540
  • 2
  • 10
  • Thank you for your help. – patwary May 25 '16 at 15:03
  • Hi Praveen, you've got the acceptance, nice for you... But could you please explain, why you are doing this so complicated with reversing back and forth and over and over again? – Shnugo May 27 '16 at 09:15