none
Syntax Issue removing characters RRS feed

  • Question

  • Hello-

    I’m having some syntax issues and hoping someone here can assist. I have a file feed that I parse out and import into a table, column named AccountNumber. I just noticed there are records erroring out due to account numbers having extra character at the end that start with ‘SPP’ and everything after that. I need to remove everything starting from ‘SPP’ all the way to the right. I’ve tried using CHARINDEX,  REPLACE, LTRIM etc. but cannot seem to get this to work properly.  Example of the random account numbers below.

    Currently Named:

    ESE02012066SPPCAN -

    FSE00788125SPPCAN DA

    JSE01845844ASPPCAN-C

    OSE01543406SPPCAN AL

    DSA1428142SPPCAN BAR

    ASA1804523SPPCAN ANN

    FSE01830551SPPCAN- N

    How I need them to be:

    ESE02012066

    FSE00788125

    JSE01845844A

    OSE01543406

    DSA1428142

    ASA1804523

    FSE01830551

    Any assistance is appreciated!!

    Friday, July 19, 2019 1:28 PM

All replies

  • Hi MikeMiller,

    Please use below code and it will solve your problem

       
    if OBJECT_ID('tempDB..#test','u') IS NOT NULL
    drop table #test
    create table #test (samplestr varchar(50))
    insert into #test
    select 'ESE02012066SPPCAN'
    UNION ALL
    SELECT 'FSE00788125SPPCAN DA'
    UNION ALL
    SELECT  'JSE01845844ASPPCAN-C'
    UNION ALL
    SELECT 'OSE01543406SPPCAN AL'
    UNION ALL
    SELECT  'DSA1428142SPPCAN BAR'
    UNION ALL
    SELECT  'ASA1804523SPPCAN ANN'
    UNION ALL
    SELECT 'FSE01830551SPPCAN- N'
    
    select  substring(samplestr,1,CHARINDEX('SPP', samplestr)-1) 
    from #test
     



    Thanks

    Srinivasa Rao G, MCSE(Business Intelligence) Blog: TechTalksPro

    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit



    Friday, July 19, 2019 1:32 PM
  • Hi MikeMiller,

    Try like below

    select  substring('ESE02012066SPPCAN',1,CHARINDEX('SPP', 'ESE02012066SPPCAN')-1) 


    Thanks

    Srinivasa Rao G, MCSE(Business Intelligence) Blog: TechTalksPro

    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit

    Thanks Srinivasa but the issue is that all I provided was a few examples. There are hundreds of randomly named account numbers with random character lengths. So I'm trying to come up with code that I don't have to provide the account number, just remove everything from SPP and all the way to the right of it. Thanks for your reply however!
    Friday, July 19, 2019 1:37 PM
  • MikeMiller,

    Please try the code which I have placed just now


    Thanks

    Srinivasa Rao G, MCSE(Business Intelligence) Blog: TechTalksPro

    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit

    Friday, July 19, 2019 1:39 PM
  • MikeMiller,

    Please try the code which I have placed just now


    Thanks

    Srinivasa Rao G, MCSE(Business Intelligence) Blog: TechTalksPro

    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit

    OK maybe I'm not explaining myself correctly. These feeds will be coming in daily with random account numbers therefore it wouldn't be feasible to manually search and correct these each day. I know how to write the code that you provided but I need to come up with code that will remove the characters 'SPP' and everything to the right of it without providing anything to the left of 'SPP'. Thanks again for your reply!
    Friday, July 19, 2019 1:45 PM
  • Stop assuming. Try the code and determine if it works or not. If it does not, post some sample data along with the value returned by the expression that does not meet your expectations. 
    Friday, July 19, 2019 1:50 PM
  • Hi MikeMillerJ,

    Please try the following:

    DECLARE @tbl TABLE	(AccountNumber VARCHAR(MAX));
    
    INSERT INTO @tbl
    VALUES ('ESE02012066SPPCAN')
    ,('FSE00788125SPPCAN DA')
    ,( 'JSE01845844ASPPCAN-C')
    ,('OSE01543406SPPCAN AL')
    ,( 'DSA1428142SPPCAN BAR')
    ,( 'ASA1804523SPPCAN ANN')
    ,('FSE01830551SPPCAN- N');
    
    DECLARE @separator CHAR(3) = 'SPP';
    SELECT AccountNumber AS [Before]
    	, PARSENAME(REPLACE(AccountNumber, @separator,'.'),2) AS [After]
    FROM @tbl;

    Output:

    Before	After
    ESE02012066SPPCAN	ESE02012066
    FSE00788125SPPCAN DA	FSE00788125
    JSE01845844ASPPCAN-C	JSE01845844A
    OSE01543406SPPCAN AL	OSE01543406
    DSA1428142SPPCAN BAR	DSA1428142
    ASA1804523SPPCAN ANN	ASA1804523
    FSE01830551SPPCAN- N	FSE01830551

    Friday, July 19, 2019 2:15 PM
  • Run the code - Srini gave you a complete script. Does it work or not? Based on your first description and sample data, it does EXACTLY what you requested. Here is a fiddle with his code as a demo.

    If something is missing, then be specific and give an example where it does not meet your needs. 

    Friday, July 19, 2019 2:20 PM
  • MikeMiller,

    Not sure whether you get a chance to check this code. Please check this code and try 

    if OBJECT_ID('tempDB..#test','u') IS NOT NULL
    drop table #test
    create table #test (samplestr varchar(50))
    insert into #test
    select 'ESE02012066SPPCAN'
    UNION ALL
    SELECT 'FSE00788125SPPCAN DA'
    UNION ALL
    SELECT  'JSE01845844ASPPCAN-C'
    UNION ALL
    SELECT 'OSE01543406SPPCAN AL'
    UNION ALL
    SELECT  'DSA1428142SPPCAN BAR'
    UNION ALL
    SELECT  'ASA1804523SPPCAN ANN'
    UNION ALL
    SELECT 'FSE01830551SPPCAN- N'
    
    select  substring(samplestr,1,CHARINDEX('SPP', samplestr)-1) 
    from #test


    Thanks

    Srinivasa Rao G, MCSE(Business Intelligence) Blog: TechTalksPro

    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit

    Friday, July 19, 2019 2:22 PM
  • Try this one:

    select  
    coalesce (Stuff(samplestr,CHARINDEX('SPP', samplestr),len(samplestr),''),samplestr) as samplestr
    from #test

    Friday, July 19, 2019 2:29 PM
    Moderator
  • Try this one:

    select  
    coalesce (Stuff(samplestr,CHARINDEX('SPP', samplestr),len(samplestr),''),samplestr) as samplestr
    from #test

    Yes that seems to be what I'm needing it to do. I was pretty sure I was going to right direction using CHARINDEX but couldn't get it to work. THANKS Jingyang!
    Friday, July 19, 2019 2:46 PM
  • The query needs to handle the case of normal accountnumber without the 'SPP' value in it to avoid the substring error.

    Good to know the query will work for you.

    Friday, July 19, 2019 2:49 PM
    Moderator
  • The query needs to handle the case of normal accountnumber without the 'SPP' value in it to avoid the substring error.

    Good to know the query will work for you.

    Exactly! That's why I changed it from SPP to SPPCAN instead :)


    Friday, July 19, 2019 3:34 PM