SQL Concat Help

This one should be easy.....

I need a SQL script that will concat my field Original_Num to only show the first characters before any '.'

For example:

SHP001234.1 should be SHP001234
SHP001234.2 should be SHP001234
SHP001234. should be SHP001234
SELECT  Sales_Doc_Type,
        Sales_Doc_Num,
        Original_Num
FROM    dbo.tspvSalesDocumentHistory

Open in new window

r270baAsked:
Who is Participating?
 
Lee SavidgeConnect With a Mentor Commented:
With you columns and table.

SELECT  Sales_Doc_Type,
        Sales_Doc_Num,
        left(Original_Num, charindex('.', Original_Num)-1)
FROM    dbo.tspvSalesDocumentHistory
0
 
Lee SavidgeCommented:
select left(myField, charindex('.', myField)-1)
0
 
r270baAuthor Commented:
Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Lee SavidgeCommented:
Does the column contain a null?
0
 
Lee SavidgeCommented:
SELECT  Sales_Doc_Type,
        Sales_Doc_Num,
        isnulll(left(Original_Num, charindex('.', Original_Num)-1), '') as Original_Num
FROM    dbo.tspvSalesDocumentHistory
0
 
r270baAuthor Commented:
Actually...not ALL Original_Num will contain the '.'  The below code works...however sometimes the Original_Num will contain just the '.' at the end and sometimes it will contain a '.#' (.1, .2, etc).

Will that always work?
SELECT  Sales_Doc_Type,
        Sales_Doc_Num,
        original_num,
        [new]=CASE WHEN ORIGINAL_num LIKE '%.%' THEN left(Original_Num, charindex('.', Original_Num)-1) ELSE Original_Num end
FROM    dbo.tspvSalesDocumentHistory
WHERE Sales_Doc_Num='i038472'

Open in new window

0
 
dqmqCommented:
SELECT  Sales_Doc_Type,
        Sales_Doc_Num,
        original_num,
 left(Original_Num+ '.', charindex('.', Original_Num+''.')-1) as NewNum
FROM    dbo.tspvSalesDocumentHistory
WHERE Sales_Doc_Num='i038472'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.