First Time using SUBSTRING

Question

First Time using SUBSTRING

Bron Tamulis

Ten Centuries

Points: 1090
More actions
July 7, 2020 at 11:37 pm

#3768251

Okay - I have a text field that i need to 'wildcard' search and return a string value.......I settled in on SUBSTRING and charindex and I am real close - but can not figure out why some results have space then the string i am looking for - I am hoping from the picture someone can suggest improving my case statement.........thanks in advance.......I really tried to solve myself.....

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

dkultasev SSC Rookie Points: 34 More actions · Answer 1

First of all, don't add SQL code as a picture, add it as a text. There is "{;} Insert/edit code sample" button to make the code highlighted properly.

According to your issue, then you get the position of ORD and then substract 3 from it, so you get some whitespaces as well. Most probably you have 3 spaces there and in some cases 2 of them (row #109).

What you need to do is to something like that:

DECLARE @test VARCHAR(100) = 'PO FT20-04-29   ORD00043330'
SELECT
CASE
WHEN @test LIKE 'PO%' THEN SUBSTRING(@test, CHARINDEX('ORD', @test), 11)
END

If the length that code is always 11 then it is much easier to use RIGHT function:

DECLARE @test VARCHAR(100) = 'PO FT20-04-29   ORD00043330'
SELECT
CASE
WHEN @test LIKE 'PO%' THEN RIGHT(@test, 11)
END

Steve Collins SSCrazy Points: 2152 More actions · Answer 2

Fwiw, adding meta data like ('ORD'+number and 'MO'+date) to database keys is not the best practice. If you're stuck with it perhaps it's possible to add constraints which only allow those characters once. Maybe there are already constraints?

Imo there are potential issues with searching for 'PO%'. Without knowing the server-level collation of your database (and assuming it's the factory default SQL_Latin1_General_CP1_CI_AS), the search will be case insensitive. Since "po" is not an uncommon letter combination... The picture says to "grab ORD value" which seems like a safer approach. How much checking is necessary depends on an assessment of the underlying data. Here's checking the 4th character to make sure it's an integer.

--declare @test varchar(100) = 'PO FT20-04-29   ORD00043330';
--declare @test varchar(100) = 'order ORD00043330 blah blah blah';
declare @test varchar(100) = 'ORDxx ORD00043330 blah';
declare @test_ndx char(11) = substring(@test, charindex('ORD', @test collate sql_latin1_general_cp1_cs_as), 11);

select
  iif(substring(@test_ndx, 4, 1) like '[0-9]', @test_ndx, 'Stock') Demand;

This reply was modified 4 years, 4 months ago by Steve Collins.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Bron Tamulis Ten Centuries Points: 1090 More actions · Answer 3

Thanks for the reply. I will refrain from pasting picture of code.

A little background - MO = Manufacturing Order. Dataset has one record for every Manufacturing Order. Objective is to give end user ability to filter and get all Manufacturing Order Records pertain to a single Sales Order (Always starts with ORD and always part of the string MO Description).

The final code that works perfectly (at least so far)

CASE WHEN A.DSCRIPTN like 'From%' then SUBSTRING(A.DSCRIPTN, 9, 11)

WHEN A.DSCRIPTN like 'ORD%' then SUBSTRING(A.DSCRIPTN, 1, 11)

WHEN A.DSCRIPTN like 'PO%' then SUBSTRING(A.DSCRIPTN, CHARINDEX('ORD', A.DSCRIPTN),11)

else 'STOCK' end as Demand,

I took out the -3 and changed the 14 to an 11. I thought -3 was needed to go back from 'ORD' (which is 3 characters) didn't realize it was going back from the start of ORD and thus, giving me blank spaces left of the ORD value. Fortunately the ORD value is always 11 characters long - which means once you find the ORD - you get the proper value.....

Thanks again - it's great I can use SUBSTRING again if I have to.

Steve Jones - SSC Editor SSC Guru Points: 736129 More actions · Answer 4

If you have extra spaces, once you substring, you can also use TRIM to clear things out.