How to use substring in SQL Server

Question

Suppose I have this query.

SELECT 
    proj.refno [Reference No.], 
    proj.projname [NNNN], 
    TotalCost= '$' + CONVERT(NVARCHAR(100),cast(ROUND((cast(ship.volfinish as int) * data.price)/1000,2) as decimal(5,2)))
 FROM 
     projects proj
 INNER JOIN 
     projdata data ON proj.controlno = data.controlno 
 INNER JOIN 
     shipment ship ON data.ctrlno = ship.dctrlno 
 WHERE 
     proj.refno IN ('item1', 'item2','item3') 
 ORDER BY 
     proj.refno

with this output:

    Reference No.           NNNN                                                TotalCost
GR-NFS52  abc123             StudentsTitle123 (NNNN: xxxxxxxxxxxxx)                                $215.45
GR-PFS53  def456             StudentsTitle456 (NNNN: xxxxxxxxxxxxx)                                $259.55
GR-SSFS43   ghi789         StudentsTitle789 (NNNN: xxxxxxxxxxxxx)                           $242.35

How can I make the NNNN column used the substring function with this output. Cause I'm not into t-sql.

NNNN
xxxxxxxxxxxxx
xxxxxxxxxxxxx
xxxxxxxxxxxxx

404notfound · Accepted Answer · 2016-06-02 07:58:58Z

1

Assuming you have pattern like NNNN: xxxxxxxxxxx) in your strings you can extract this number using some simple manipulation over the string value using charindex and substring:

declare @str nvarchar(max)
select @str = 'Students (NNNN: 9781410314291)'

select substring(@str, 
               charindex('ISBN:', @str) + 6, 
               charindex(')', @str, charindex('NNNN:', @str)) - charindex('NNNN:', @str) - 6)

Here we first find position of NNNN: substring, then position of first occurence of closing bracket ) after this substing and taking part of string between these positions - it is exactly number you need.

In your particular case you can use outer apply in select query in order to make it more readable by avoiding multiple copy-pasting the same charindex('NNNN:', proj.projname) expression:

select
    proj.refno [Reference No.], 
    substring(proj.projname, 
             CALC.pos_from, 
             charindex(')', proj.projname, CALC.pos_from) - CALC.pos_from - 6) as [NNNN],
    ....
FROM projects proj
  .....
   outer apply (select charindex('NNNN:', proj.projname) as pos_from) as CALC

edited Jun 2, 2016 at 7:58

404notfound

779 bronze badges

answered Jan 20, 2016 at 8:44

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

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

2 Comments

404notfound Over a year ago

it displays 9781410314291) i try to use -1 after the ) but it doesn't work so I can remove the close parenthesis

Andrey Korneyev Over a year ago

@404notfound Oh, it was a typo in the answer. Actually it should be -6 there, since it is calculation of length of number to extract and we should decrease it by length of ISBN: prefix which is exactly 6. This is needed due to our start position determined at first step - is start position of ёШЫИТё prefix, not the number itself.

t-clausen.dk · Accepted Answer · 2016-01-20 09:07:30Z

1

Try this:

DECLARE @str nvarchar(max) = 'Novels for Students, vol. 52 (ISBN: 9781410314291)'

SELECT 
  REPLACE(STUFF(@str, 1, PATINDEX('% '+REPLICATE('[0-9]', 13) + '%', @str), ''), ')', '')

Result:

9781410314291

answered Jan 20, 2016 at 9:07

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

3 Comments

404notfound Over a year ago

thanks! I think I will now be studying stuff, patindex and replicate (Up vote for this)

eftpotrm Over a year ago

Little thing - older ISBNs can be 10 characters instead of 13. It's safer to detect the length than hardcode them in this case.

t-clausen.dk Over a year ago

@eftpotrm to be honest, I had no clue what ISBN was. This was a way to detect a pattern, I used 13, because that matched the test data. If data has a different length, it should be easy to adjust the script

Collectives™ on Stack Overflow

How to use substring in SQL Server

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related