Select only part of a string SQL Server

Question

I have the below query:

Select
    Notes
From
    Table

And a sample out put may be:

78652972MWGN00183HCE736430
77305311FTBCHCE735946Wheat
Training

However is what I would like as the output is an ID within the string, the ID always starts with HCE and is always 8 characters long, and where the is no ID, it would return No Machine ID

So my desired out put for the above would be:

HCE736430
HCE735946
No Machine Id

How can I achieve this ?

I have looked at using SUBSTRING, but cannot search for the HCE part.

I am currently doing this in Excel using MID and SEARCH:

=MID(A2,SEARCH("HCE",A2),9)

Look at CHARINDEX. learn.microsoft.com/en-us/sql/t-sql/functions/… — SteveB
– SteveB, Commented Dec 6, 2018 at 15:38

Sean Lange · Accepted Answer · 2018-12-06 16:21:57Z

5

You can use PATINDEX for this. Here is an example.

declare @Something table (SomeVal varchar(50))

insert @Something values
('78652972MWGN00183HCE736430')
, ('77305311FTBCHCE735946Wheat')
, ('Training')
,('asdfHCExx')



select case when patindex('%HCE[0-9a-z][0-9a-z][0-9a-z][0-9a-z][0-9a-z]%', SomeVal) > 0
    then substring(SomeVal, patindex('%HCE[0-9a-z][0-9a-z][0-9a-z][0-9a-z][0-9a-z]%', SomeVal), 9)
    else 'No Machine Id'
end

from @Something

edited Dec 6, 2018 at 16:21

answered Dec 6, 2018 at 15:42

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

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

1 Comment

Sean Lange Over a year ago

I updated this to handle a length of 8 characters. Not very clear what the OP expects for valid characters.

Rono · Accepted Answer · 2018-12-06 15:44:35Z

2

This query does what you are asking for:

SELECT CASE WHEN  CHARINDEX('HCE', Notes) = 0 THEN 'No MachineID' ELSE 
SUBSTRING(Gender, CHARINDEX('HCE', Notes), 9) END
FROM  Table

answered Dec 6, 2018 at 15:44

Rono

3,4012 gold badges41 silver badges62 bronze badges

3 Comments

Sean Lange Over a year ago

This may return false positives if they have HCE in the string not followed by 6 digits.

Rono Over a year ago

That was not part of the specification, actually.

Sean Lange Over a year ago

"the ID always starts with HCE and is always 8 characters long". This isn't necessarily incorrect, just saying it could possibly return false positives.

Yogesh Sharma · Accepted Answer · 2018-12-06 15:46:04Z

2

You can use patindex() with substring() :

SELECT Notes, SUBSTRING(Notes, PATINDEX('%HCE%', Notes +'HCE'), 9)
From Table;

First version will display blank if the Machine Id note found if you want to display No Machine Id instead then you can add case expression :

(CASE WHEN PATINDEX('%HCE%', Notes) > 0
      THEN SUBSTRING(Notes, PATINDEX('%HCE%', Notes), 9)
      ELSE 
      'No Machine Id'
 END)

edited Dec 6, 2018 at 15:46

answered Dec 6, 2018 at 15:40

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

3 Comments

Sean Lange Over a year ago

This may return false positives if they have HCE in the string not followed by 6 digits.

Yogesh Sharma Over a year ago

@SeanLange. . . I have a read this the ID always starts with HCE and is always 8 characters long,

Sean Lange Over a year ago

Maybe it is just characters and I interpreted it as digits. But This could still return false positives if HCE falls towards then end. Not saying this is wrong, just saying it may return false positives. :)

Collectives™ on Stack Overflow

Select only part of a string SQL Server

3 Answers 3

1 Comment

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related