Extracting string and inserting into another column in another table

Transact-SQL
1

Hello All,

I am new to SQL. I need help with below.

I need to extract a specific text from a string in a table column (Log table) which is actually a date (InactivationDate) and there is a value after this in the string. I need to extract this date value and insert it into another column in another table where there is a column named InactivationDate.

Can you let me know how to do this?

Thank you

1 Like
2

You'll have to provide specific examples of the strings to be searched.
Otherwise, all we can do is point you to the documentation for string functions and wish you luck.

1 Like
3

Okay, the Column name is LogRecordData which comes from a table named ContentLog. The Column LogRecordData starts and contains other texts including for e.g. 'InactivationDate: NULL -> 2023-12-20 07:55:29'. I need to extract the date portion after the text InactivationDate from here and insert it in to another column in another SQL table which has a column named InactivationDate. I hope this info is sufficient.

Regards

4

So you need '2023-12-20', and not the NULL or whatever might be in that spot if it's not NULL?

5 
SELECT 
  LogRecordData
, CHARINDEX('-> ', LogRecordData) AS [-> Location]
, CONVERT(datetime2(0), SUBSTRING(LogRecordData, CHARINDEX('-> ', LogRecordData) + 3, 19)) AS [The Date]
FROM ContentLog;
6

Yes, I do not need the NULL (sorry for not making it clear in my reply), just the date without apostrophe symbol as well. Can I try the query provided by you.

Thank you

7

I used the query but got the below error message.

Msg 241, Level 16, State 1, Line 6
Conversion failed when converting date and/or time from character string.

8

Hi

Can you please let me know your answer.

Regards

9

To help you troubleshoot that, I'd need to know the value that triggered the error. Most likely there are entries that have a -> character in them, but what comes afterward is not a valid date. The only thing I can recommend is to change CONVERT() to TRY_CONVERT() and when it fails, it will return NULL rather than cause an error.

10

You must provide an example that correctly represents the context in which you find yourself.
A few lines are enough.

Start with something like this:

DECLARE @MyLogTable AS TABLE
(
	LogRecordData VARCHAR(100)
)
INSERT INTO @MyLogTable (LogRecordData) VALUES 
	('InactivationDate: NULL -> 2020-01-12 12:07:16')
	, ('InactivationDate: NULL -> 2022-08-01 09:43:27')
	, ('InactivationDate: NULL -> 2023-12-20 07:55:29')

such that you provide the structure of a test table and some meaningful data corresponding to your real data.
In this way it becomes feasible to investigate any errors you find because we would have a common environment on which to work.

11

hi

hope this helps

assumes data is always going to be in this format

create sample data script

DECLARE @MyLogTable AS TABLE
(
LogRecordData VARCHAR(100)
)
INSERT INTO @MyLogTable (LogRecordData) VALUES
('InactivationDate: NULL -> 2020-01-12 12:07:16')
, ('InactivationDate: NULL -> 2022-08-01 09:43:27')
, ('InactivationDate: NULL -> 2023-12-20 07:55:29')

select 
  LogRecordData
  , right(LogRecordData, len(LogRecordData)-CHARINDEX('-> ', LogRecordData)-2)
from 
  @MyLogTable

image
image697×159 6.01 KB

12

Hi Harish

Thanks for your response.

I will try your query, but I want to know if it will work irrespective of the position of the Inactivation date text in the string, because it's not in the same position in all the column data so I wanted to confirm with you before I try this.

Regards

13

my solution will not work

14

Hi Harish

Can you provide another alternative way that will work in my case.

Regards
Rajagopallan

15

Until you can provide representative examples of your data, it's difficult for people to create solutions that will work for you.

2 Likes
16

Thank you

17

Hi

Hope this helps

create data script

DECLARE @MyLogTable AS TABLE
(
LogRecordData VARCHAR(100)
)
INSERT INTO @MyLogTable (LogRecordData) VALUES
('aaaaaa bbbbbb ccccc ddd InactivationDate: NULL -> 2020-01-12 12:07:16 xxxxxxx yyyyyyy'),
('ccccc ddd InactivationDate: NULL -> 2020-01-12 12:07:16 xxxxxxx yyyyyyy ssssss ttt ')

SELECT  
  LogRecordData
  , substring(LogRecordData,patindex('% -> %',LogRecordData)+3,20) 
FROM 
  @MyLogTable

image