Extract string from column in SQL Server 2012

Question

I am trying to extract a certain amount of length in string if the specific value is available inside that column. The column however has a lot of data in it which where the value MODEL_NUMBER may be present.

I am trying to only extract 10 characters to the right of MODEL_NUMBER. I hope my explanation makes sense.

select *
from dbo.table
where dbo.table.column like '%MODEL_NUMBER =%'

Example of data within dbo.table.column which isn't constant so the position can always change:

if ('A' != 'D' and '' != '1') translate for simple rules where  never_have_this_argument = 'A'  and EVT_ID = 'SKYP_ER'  and IF_ID = 'TEAM_DCS_IFD'  and IF_DATA_SEQ = 1234567  and EVT_DATA_SEQ = 8866848  and DEST_SYS_ID = 'DCS'  and IFD_ACTION = '[ALL]'  and EVT_IF_DATA_SEQ = 8925456  and   and MODEL_NUMBER= 312963532_1'  and COMM_MODE_CD = 'SYNCD'  and COMM_MTHD_ID = ''  and MODE = 'SEAMLES'

SQL Server 2012 is no longer supported... it doesn't even get critical security patches any more. Migrating to a supported version is job #1 here. — Joel Coehoorn
– Joel Coehoorn, Commented Apr 2, 2024 at 15:54
Also, when you start needing to look inside large text fields like this is the time when you need to start thinking about expanding to the data model to include the relavant items in their own columns at insert/update time. The performance will be, without any exaggeration, multiple orders of magnitude better. — Joel Coehoorn
– Joel Coehoorn, Commented Apr 2, 2024 at 15:57
The string doesn't even look vaguely like something like JSON or something similar (not that that's too helpful, considering how old a version fo SQL Server you're on); it's just a mess (there's even blank parts, such as where you have and EVT_IF_DATA_SEQ = 8925456 and and MODEL_NUMBER= 312963532_1'). Some values have quotes around them, others don't (and one is only quoted on the right hand side)... You really need to fix the design here, that is the only real solution, and future you will be extremely greatful. — Thom A
– Thom A ♦, Commented Apr 2, 2024 at 16:09
CHARINDEX and SUBSTRING are likely to be of help. If you try using them and can't get it working, please edit your question and show us the code you've tried. — Andrew Morton
– Andrew Morton, Commented Apr 2, 2024 at 16:12
%MODEL_NUMBER =% but your string has MODEL_NUMBER= 312963532_1 which won't match already since space is on the wrong side of the equals. Also, you should include your desired result for this string. But feel free to play around with select substring(x.str, patindex('%MODEL_NUMBER= %', x.str) + datalength('MODEL_NUMBER= '), 10) from yourtable x — siggemannen
– siggemannen, Commented Apr 2, 2024 at 16:18

Ian Boyd · Accepted Answer · 2024-04-02 18:26:04Z

0

If the goal is to look in the string for MODEL_NUMBER=, and extract the 10 characters after it, from an example string like:

if ('A' != 'D' and '' != '1') translate for simple rules where never_have_this_argument = 'A' and EVT_ID = 'SKYP_ER' and IF_ID = 'TEAM_DCS_IFD' and IF_DATA_SEQ = 1234567 and EVT_DATA_SEQ = 8866848 and DEST_SYS_ID = 'DCS' and IFD_ACTION = '[ALL]' and EVT_IF_DATA_SEQ = 8925456 and and MODEL_NUMBER= 312963532_1' and COMM_MODE_CD = 'SYNCD' and COMM_MTHD_ID = '' and MODE = 'SEAMLES'

(emphasis mine)

Then you can use:

SELECT
   SUBSTRING(column_name, CHARINDEX('MODEL_NUMBER=', column_name) + LEN('MODEL_NUMBER='),  10) AS ExtractedModelNumber
FROM dbo.table
WHERE column_name LIKE '%MODEL_NUMBER=%'

edited Apr 2, 2024 at 18:26

answered Apr 2, 2024 at 17:53

Ian Boyd

259k272 gold badges920 silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extract string from column in SQL Server 2012

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related