none
FIND THE VALUE AND SAVE RRS feed

  • Question

  • Hi

    i have to find the third  position  have to store in table pls help me out


    example 1

    Declare @col varchar(500)
    set @col='MMT/IMPE/924209522194/transfer/SRI LAKSHM/ANDHRA BANK'


    RESULT
    924209522194

    example 2
    Declare @col varchar(500)
    set @col='MMTEE/IMPES/1234566/transfer/Bas/ICC'


    RESULT
    1234566


    baskarlakshmi

    Friday, August 30, 2019 9:56 AM

Answers

  • Try this..

    create table test(value varchar(30))
    
    Declare @col varchar(500)
    set @col='MMT/IMPE/924209522194/transfer/SRI LAKSHM/ANDHRA BANK'
    
    INSERT into test SELECT PARSENAME(REPLACE(SUBSTRING(SUBSTRING(@col, CHARINDEX('/', @col)+1, LEN(@col)), CHARINDEX('/', SUBSTRING(@col, CHARINDEX('/', @col)+1, LEN(@col)))+1, LEN(@col)), '/', '.'), 4) AS RESULT
    
    select * from test
    • Marked as answer by baskaLakshmi Saturday, August 31, 2019 9:47 AM
    Friday, August 30, 2019 12:11 PM
  • 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
    Declare @data TABLE (col VARCHAR(500));
    INSERT INTO @data (col) VALUES
    ('MMT/IMPE/924209522194/transfer/SRI LAKSHM/ANDHRA BANK'),
    ('MMTEE/IMPES/1234566/transfer/Bas/ICC')
    
    SELECT s.val
    FROM @data d
        CROSS APPLY dbo.split(d.col,'/') s
    WHERE s.id = 3;
    

    • Marked as answer by baskaLakshmi Saturday, August 31, 2019 9:49 AM
    Friday, August 30, 2019 12:39 PM
    Moderator
  • create table t1(col1 varchar(200))
    insert t1 values ('MMT/IMPE/924209522194/transfer/SRI LAKSHM/ANDHRA BANK')
    insert t1 values ('MMTEE/IMPES/1234566/transfer/Bas/ICC')

    select *, stuff(substring(right(col1,len(col1)-charindex('/',col1)),
              charindex('/',right(col1,len(col1)-charindex('/',col1)))+1,
              len(col1)),charindex('/',
              substring(right(col1,len(col1)-charindex('/',col1)),
              charindex('/',right(col1,len(col1)-charindex('/',col1)))+1,
              len(col1))),len(col1),NULL)                     
    from t1

    • Marked as answer by baskaLakshmi Saturday, August 31, 2019 9:52 AM
    Friday, August 30, 2019 1:12 PM
  • Hi Tiruttani_baskaLakshmi,

    you can also use the below code(another way),

    DECLARE @sample TABLE(
    	sample_data VARCHAR(500)
    );
    
    INSERT INTO @sample 
    VALUES 
    ('MMT/IMPE/924209522194/transfer/SRI LAKSHM/ANDHRA BANK'),
    ('MMTEE/IMPES/1234566/transfer/Bas/ICC');
    
    DECLARE @position INT ;
    SET @position=3; --> give here 'n' letter as input as per requirement
    
    SELECT sample_data,RTRIM(LTRIM(x.i.value('.','VARCHAR(20)'))) AS required_data 
    FROM 
    (
     SELECT sample_data,CONVERT(XML,'<i>'+REPLACE(sample_data,'/','</i><i>')+'</i>') AS required_data 
     FROM @sample
    )x1 
    CROSS APPLY required_data.nodes('i[position()=sql:variable("@position")]') AS x(i);
    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    • Marked as answer by baskaLakshmi Saturday, August 31, 2019 11:15 AM
    Saturday, August 31, 2019 11:01 AM
  • Use a scalar UDF with an index should be easy solution.

    You can see an example with recursive cte:

    DECLARE @col VARCHAR(500)
    SET @col = 'MMTEE/IMPES/1234566/transfer/Bas/ICC'
    --'MMT/IMPE/924209522194/transfer/SRI LAKSHM/ANDHRA BANK'
     
    ;with mycte as (
    Select 1 as seq,  1 as j, @col as col, CHARINDEX ('/', @col) as i, Cast(Stuff(@col,1,CHARINDEX ('/', @col),'') as  VARCHAR(500))  col2
    union all
    Select m.seq+1 as seq, m.i+1 as j, m.col,  m.i+CHARINDEX ('/', m.col2) i, 
    Cast(Stuff(m.col2,1,CHARINDEX ('/', m.col2),'') as varchar(500)) col2 
    from mycte m
    Where  CHARINDEX ('/', m.col2)>0
    
    )
    
    select Substring(col,j,i-j) as Result from mycte
    WHERE seq=3

    • Marked as answer by baskaLakshmi Saturday, August 31, 2019 9:52 AM
    Friday, August 30, 2019 1:47 PM
    Moderator

All replies

  • Try this..

    create table test(value varchar(30))
    
    Declare @col varchar(500)
    set @col='MMT/IMPE/924209522194/transfer/SRI LAKSHM/ANDHRA BANK'
    
    INSERT into test SELECT PARSENAME(REPLACE(SUBSTRING(SUBSTRING(@col, CHARINDEX('/', @col)+1, LEN(@col)), CHARINDEX('/', SUBSTRING(@col, CHARINDEX('/', @col)+1, LEN(@col)))+1, LEN(@col)), '/', '.'), 4) AS RESULT
    
    select * from test
    • Marked as answer by baskaLakshmi Saturday, August 31, 2019 9:47 AM
    Friday, August 30, 2019 12:11 PM
  • Hi

    i have to find the third  position  have to store in table pls help me out

    You can create a function and use it to get the desired result:

    Please refer: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8c179086-d1e9-4e8b-a4ab-b546cf4589b4/how-to-select-the-nth-word-in-a-string?forum=transactsql


    Cheers
    Vaibhav
    MCSA (SQL Server 2014)


    Friday, August 30, 2019 12:34 PM
  • 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
    Declare @data TABLE (col VARCHAR(500));
    INSERT INTO @data (col) VALUES
    ('MMT/IMPE/924209522194/transfer/SRI LAKSHM/ANDHRA BANK'),
    ('MMTEE/IMPES/1234566/transfer/Bas/ICC')
    
    SELECT s.val
    FROM @data d
        CROSS APPLY dbo.split(d.col,'/') s
    WHERE s.id = 3;
    

    • Marked as answer by baskaLakshmi Saturday, August 31, 2019 9:49 AM
    Friday, August 30, 2019 12:39 PM
    Moderator
  • create table t1(col1 varchar(200))
    insert t1 values ('MMT/IMPE/924209522194/transfer/SRI LAKSHM/ANDHRA BANK')
    insert t1 values ('MMTEE/IMPES/1234566/transfer/Bas/ICC')

    select *, stuff(substring(right(col1,len(col1)-charindex('/',col1)),
              charindex('/',right(col1,len(col1)-charindex('/',col1)))+1,
              len(col1)),charindex('/',
              substring(right(col1,len(col1)-charindex('/',col1)),
              charindex('/',right(col1,len(col1)-charindex('/',col1)))+1,
              len(col1))),len(col1),NULL)                     
    from t1

    • Marked as answer by baskaLakshmi Saturday, August 31, 2019 9:52 AM
    Friday, August 30, 2019 1:12 PM
  • Use a scalar UDF with an index should be easy solution.

    You can see an example with recursive cte:

    DECLARE @col VARCHAR(500)
    SET @col = 'MMTEE/IMPES/1234566/transfer/Bas/ICC'
    --'MMT/IMPE/924209522194/transfer/SRI LAKSHM/ANDHRA BANK'
     
    ;with mycte as (
    Select 1 as seq,  1 as j, @col as col, CHARINDEX ('/', @col) as i, Cast(Stuff(@col,1,CHARINDEX ('/', @col),'') as  VARCHAR(500))  col2
    union all
    Select m.seq+1 as seq, m.i+1 as j, m.col,  m.i+CHARINDEX ('/', m.col2) i, 
    Cast(Stuff(m.col2,1,CHARINDEX ('/', m.col2),'') as varchar(500)) col2 
    from mycte m
    Where  CHARINDEX ('/', m.col2)>0
    
    )
    
    select Substring(col,j,i-j) as Result from mycte
    WHERE seq=3

    • Marked as answer by baskaLakshmi Saturday, August 31, 2019 9:52 AM
    Friday, August 30, 2019 1:47 PM
    Moderator
  • If your SQL version is 2016 or higher, try this:

    DECLARE @col varchar(500);
    SET @col='MMT/IMPE/924209522194/transfer/SRI LAKSHM/ANDHRA BANK';
    
    DECLARE @T TABLE (
    	RowId int IDENTITY(1, 1),
    	Item varchar(20)
    );
    INSERT INTO @T (Item)
    SELECT value
    FROM STRING_SPLIT(@col, '/');
    
    SELECT Item FROM @T WHERE RowId = 3;


    A Fan of SSIS, SSRS and SSAS

    Friday, August 30, 2019 2:10 PM
  • If your SQL version is 2016 or higher, try this:

    DECLARE @col varchar(500);
    SET @col='MMT/IMPE/924209522194/transfer/SRI LAKSHM/ANDHRA BANK';
    
    DECLARE @T TABLE (
    	RowId int IDENTITY(1, 1),
    	Item varchar(20)
    );
    INSERT INTO @T (Item)
    SELECT value
    FROM STRING_SPLIT(@col, '/');
    
    SELECT Item FROM @T WHERE RowId = 3;

    (This is probably a good solution for other future versions of MSSQL, according to https://feedback.azure.com/forums/908035-sql-server/suggestions/32902852-string-split-is-not-feature-complete).  


    Friday, August 30, 2019 6:49 PM
  • The result order is NOT guaranteed  based on MSDN document:

    "The output rows might be in any order. The order is not guaranteed to match the order of the substrings in the input string. "

    https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017

    Friday, August 30, 2019 7:08 PM
    Moderator
  • not diffucult process

    use string_split function

    create table t (value nvarchar(100))
    
    DECLARE @col VARCHAR(500)
    
    SET @col = 'MMTEE/IMPES/1234566/transfer/Bas/ICC'
    
    insert into t
    (value)
    
    SELECT value FROM STRING_SPLIT(@col, '/');
    
    select value  from t where substring(value,1,1) in 
     ('1','2','3','4','5','6','7','8','9') 
    

    Friday, August 30, 2019 9:02 PM
  • @SQLNeophyte

    Thank you..its working


    baskarlakshmi

    Saturday, August 31, 2019 9:48 AM
  • @Tom Philips

    Thank you..its working


    baskarlakshmi

    Saturday, August 31, 2019 9:49 AM
  • Thank you .

    baskarlakshmi

    Saturday, August 31, 2019 9:52 AM
  • Hi Tiruttani_baskaLakshmi,

    you can also use the below code(another way),

    DECLARE @sample TABLE(
    	sample_data VARCHAR(500)
    );
    
    INSERT INTO @sample 
    VALUES 
    ('MMT/IMPE/924209522194/transfer/SRI LAKSHM/ANDHRA BANK'),
    ('MMTEE/IMPES/1234566/transfer/Bas/ICC');
    
    DECLARE @position INT ;
    SET @position=3; --> give here 'n' letter as input as per requirement
    
    SELECT sample_data,RTRIM(LTRIM(x.i.value('.','VARCHAR(20)'))) AS required_data 
    FROM 
    (
     SELECT sample_data,CONVERT(XML,'<i>'+REPLACE(sample_data,'/','</i><i>')+'</i>') AS required_data 
     FROM @sample
    )x1 
    CROSS APPLY required_data.nodes('i[position()=sql:variable("@position")]') AS x(i);
    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    • Marked as answer by baskaLakshmi Saturday, August 31, 2019 11:15 AM
    Saturday, August 31, 2019 11:01 AM
  • @Soume

    How can find the second position values using the above query.


    baskarlakshmi

    Friday, September 13, 2019 5:20 AM