sub string in sql server

Question

I have a string in the format . (eg 902.5)
Need to split and collect this into separate variables in sql server 2008.

    DECLARE  
       @Core VARCHAR(10)='902.5',  
       @OrgNumber VARCHAR(6) = NULL,  
       @DeptNumber VARCHAR(2) = NULL

        IF (NULLIF(@Core, '')) IS NOT NULL
        BEGIN
             SELECT @OrgNumber =SUBSTRING(@Core, 1, CHARINDEX('.', @Core) - 1),
                   @DeptNumber =SUBSTRING(@Core, CHARINDEX('.', @Core) + 1, LEN(@Core))

             SELECT @OrgNumber = upper(nullif(ltrim(nullif(rtrim(@OrgNumber ), '')), '')), 
@DeptNumber = nullif(ltrim(nullif(rtrim(@DeptNumber ), '')), '')  
        END

However this does not handle the case where Contains the Org ref only(no dept ref) e.g 902
How do I handle the case gracefully within the same statement?

Is it always the same format?

Felix Pamittan
– Felix Pamittan

2015-09-07 04:28:18 +00:00
Commented Sep 7, 2015 at 4:28 — Felix Pamittan
– Felix Pamittan, Commented Sep 7, 2015 at 4:28
Yes - it will always be x.y or simply x

IUnknown
– IUnknown

2015-09-07 04:28:53 +00:00
Commented Sep 7, 2015 at 4:28 — IUnknown
– IUnknown, Commented Sep 7, 2015 at 4:28

Pரதீப் · Accepted Answer · 2015-09-07 05:04:49Z

2

Use PARSENAME function

DECLARE @Core VARCHAR(50)= '902.2'

SELECT Parsename(@Core, CASE WHEN Charindex('.', @Core) > 0 THEN 2 ELSE 1 END) AS OrgNumber,
       Parsename(@Core, CASE WHEN Charindex('.', @Core) > 0 THEN 1 ELSE 2 END) AS DeptNumber

edited Sep 7, 2015 at 5:04

answered Sep 7, 2015 at 4:40

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

IUnknown Over a year ago

PARSENAME works from right to left - and would assign org number to dept ref if the delimiter '.' is missing.

Sateesh Pagolu · Accepted Answer · 2015-09-07 04:43:51Z

Updated your query. Also, notice that your IF (NULLIF(@Core, '')) IS NOT NULL is incorrect.

 DECLARE @Core       VARCHAR(10)='902', 
        @OrgNumber  VARCHAR(6) = NULL, 
        @DeptNumber VARCHAR(2) = NULL 

IF @Core IS NOT NULL 
  BEGIN 
      SELECT @OrgNumber = CASE 
                            WHEN Charindex('.', @Core) > 0 THEN 
                            Substring(@Core, 1, Charindex('.', @Core) 
                                                - 1) 
                            ELSE Substring(@Core, 1, Len(@Core)) 
                          END, 
             @DeptNumber = CASE 
                             WHEN Charindex('.', @Core) > 0 THEN 
                             Substring(@Core, 
                             Charindex('.', @Core) + 1 
                             , Len(@Core)) 
                             ELSE NULL 
                           END 

      SELECT @OrgNumber = Upper(NULLIF(Ltrim(NULLIF(Rtrim(@OrgNumber), '')), '') 
                          ), 
             @DeptNumber = NULLIF(Ltrim(NULLIF(Rtrim(@DeptNumber), '')), '') 
  END 

SELECT @OrgNumber, 
       @DeptNumber

Collectives™ on Stack Overflow

sub string in sql server

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related