SQLTeam.com | Weblogs | Forums

Split String

#1

Hi Guys,

Good Morning to you all. I need some help to reformat and replace some text within strings of texts.

Please find the script below:

DECLARE @splitstringTest TABLE (
filelink varchar(255),
Secondfilelink varchar(255)
);

INSERT INTO @splitstringTest ( filelink,Secondfilelink )
VALUES ('Q:\ARR.CSV','M:\QL_LETTERS\DOCCTRL.BAT M:\QL_LETTERS\MASTERS\TEN001.DOCX /mQLEDM'),
('C:\QLTEMP\CRM.CSV','M:\QL_LETTERS\DOCCTRL.BAT M:\QL_LETTERS\MASTERS\WLRTB.DOCX /MQLEDM'),
('C:\QLTEMP\TENDER.CSV','M:\QL_LETTERS\DOCCTRL.BAT M:\QL_LETTERS\MASTERS\SO0001.DOCX /mQLEDM')

Select * from @splitstringTest
Output Result:
filelink Secondfilelink
Q:\ARR.CSV M:\QL_LETTERS\DOCCTRL.BAT M:\QL_LETTERS\MASTERS\TEN001.DOCX /mQLEDM
C:\QLTEMP\CRM.CSV M:\QL_LETTERS\DOCCTRL.BAT M:\QL_LETTERS\MASTERS\WLRTB.DOCX /MQLEDM
C:\QLTEMP\TENDER.CSV M:\QL_LETTERS\DOCCTRL.BAT M:\QL_LETTERS\MASTERS\SO0001.DOCX /mQLEDM

Desired Output: I need to replace the filelink with the Newfilelink below
Newfilelink: Q:\ARR.CSV

Desired output2: I need to replace the secondfilelink with the Newsecondfilelink
NewSecondfilelink: \ah-qlapp-01\QLStore\QL_Letters\DOCCTRL.BAT \ah-qlapp-01\QLStore\QL_Letters\MASTERS\ARR070.DOCX /mQLEDM

Thanks
Bobby

0 Likes

#2

hi

I am trying to understand what you want to do ????

is this what you want ….all 3 rows filelink like this ???
Q:\ARR.CSV
Q:\ARR.CSV
Q:\ARR.CSV

0 Likes

#3

maybe this? your post is a bit confusing.

Select * , replace('\ah-qlapp-01\QLStore\QL_Letters\DOCCTRL.BAT \ah-qlapp-01\QLStore\QL_Letters\MASTERS\ARR070.DOCX /mQLEDM','ARR070.DOCX',substring(filelink,CHARINDEX('\',filelink) +1, 50)) as Newsecondfilelink
  from @splitstringTest
0 Likes

#4

I am not sure I understand your requirements - but here is my guess:

 Select newFileLink = replace(st.Filelink, 'C:\QLTEMP\', 'Q:\')
      , newSecondFileLink = replace(st.Secondfilelink, 'M:\', '\\ah-qlapp-01\QLStore\')
   From @splitstringTest st;

Here are the results:

0 Likes