locked
Split only value between special character RRS feed

  • Question

  • Hi,

    CREATE TABLE SLASH_TB
    (
    C_SLASH VARCHAR(200)
    )


    INSERT INTO SLASH_TB values('07001010002115610000.00FT/814111455781/2018-05-2104-06-2018')
    INSERT INTO SLASH_TB values('07001010002115610000.00FT/878111455795/2018-05-2104-06-2018')
    INSERT INTO SLASH_TB values('07001010002115610000.00FTPY/814566556455781/2018-05-2104-06-2018')



    I want to split only between slash value

    Expected Result

    select * from SLASH_TB

    C_SLASH

    814111455781
    878111455795
    814566556455781


    • Edited by Antonioy Monday, June 4, 2018 12:51 PM
    Monday, June 4, 2018 12:47 PM

Answers

  • Try this..

    select substring(C_SLASH, charindex('/', C_SLASH, 1) + 1 , (len(C_SLASH) - charindex('/', reverse(C_SLASH))) - charindex('/', C_SLASH, 1)) from SLASH_TB
    • Edited by SQLNeophyte Monday, June 4, 2018 1:17 PM
    • Marked as answer by Antonioy Tuesday, June 5, 2018 5:22 AM
    Monday, June 4, 2018 1:16 PM

All replies

  • CREATE FUNCTION [dbo].[split](
              @delimited NVARCHAR(MAX),
              @delimiter NVARCHAR(100)
            ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
            AS
            BEGIN
              DECLARE @xml XML
              SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
    
              INSERT INTO @t(val)
              SELECT  r.value('.','nvarchar(MAX)') as item
              FROM  @xml.nodes('/t') as records(r)
              RETURN
            END
    GO
    
    SELECT split.val AS C_SLASH
    FROM SLASH_TB slash
        CROSS APPLY dbo.split(slash.C_SLASH,'/') split
    WHERE split.id = 2


    Monday, June 4, 2018 1:00 PM
  • Try this..

    select substring(C_SLASH, charindex('/', C_SLASH, 1) + 1 , (len(C_SLASH) - charindex('/', reverse(C_SLASH))) - charindex('/', C_SLASH, 1)) from SLASH_TB
    • Edited by SQLNeophyte Monday, June 4, 2018 1:17 PM
    • Marked as answer by Antonioy Tuesday, June 5, 2018 5:22 AM
    Monday, June 4, 2018 1:16 PM
  • For this example

    declare @s varchar(1000)='07001010002115610000.00FT/814111455781/2018-05-2104-06-2018'


    SELECT SUBSTRING(@s,CHARINDEX('/',@s )+1,LEN(@s)-(CHARINDEX('/',@s )+CHARINDEX('/',REVERSE(@s))))


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, June 4, 2018 1:39 PM
    Answerer
  • Select  Stuff(reverse(C_SLASH),1,charindex('/',reverse(C_SLASH)),'')
    ,Reverse(Stuff(Stuff(reverse(C_SLASH),1,charindex('/',reverse(C_SLASH)),''), charindex('/',Stuff(reverse(C_SLASH),1,charindex('/',reverse(C_SLASH)),'')),len(Stuff(reverse(C_SLASH),1,charindex('/',reverse(C_SLASH)),'')),'') ) C_SPLASH
       from SLASH_TB 

    Monday, June 4, 2018 1:39 PM
  • Try,

    select substring( C_SLASH,PATINDEX ('%/%/%',C_SLASH)+1, len(C_SLASH)-PATINDEX ('%/%/%',REVERSE(C_SLASH))-PATINDEX ('%/%/%',C_SLASH)) from SLASH_TB


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, June 4, 2018 2:01 PM