Access to SQL expression

Question

Access to SQL expression

sullyman

SSC Enthusiast

Points: 115
More actions
December 15, 2020 at 9:16 pm

#3820889
Hi folks,
I'm trying and failing miserably to convert an Access Expression to SQL query
The MS Access Expression strips the following field data formats to extract the middle text between the 2 hyphens
2C00P02-CRIW-93201 (Extracted value: CRIW)
Any help appreciated
```
Left(Mid([LINE_NUM],IIf(Len(Left([LINE_NUM],InStr([LINE_NUM],"-")))=0,1,Len(Left([LINE_NUM],InStr([LINE_NUM],"-")))+1)),InStr(Mid([LINE_NUM],IIf(Len(Left([LINE_NUM],InStr([LINE_NUM],"-")))=0,1,Len(Left([LINE_NUM],InStr([LINE_NUM],"-")))+1)),"-")-1)
```

December 24, 2020 at 2:53 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

This can be done inline with charindex and substring or parsename - or you can create a function to parse the string elements. To get you started.

 Select MyValue = parsename(replace('2C00P02-CRIW-93201', '-', '.'), 2)

If you can have variable lengths and number of delimiters - the above would not work. It will only work for up to 4 fixed elements.

Declare @MyString varchar(30) = '2C00P02-CRIW-93201';

 Select *
      , col_01 = ltrim(substring(v.MyString, 1, p1.pos - 2))
      , col_02 = ltrim(substring(v.MyString, p1.pos, p2.pos - p1.pos - 1))
      , col_03 = ltrim(substring(v.MyString, p2.pos, p3.pos - p2.pos - 1))
   From (Values (concat(@MyString, '--')))                      As v(MyString)
  Cross Apply (Values (charindex('-', v.MyString, 1) + 1))      As p1(pos)
  Cross Apply (Values (charindex('-', v.MyString, p1.pos) + 1)) As p2(pos)
  Cross Apply (Values (charindex('-', v.MyString, p2.pos) + 1)) As p3(pos)

That can easily be converted to an inline-table valued function where you pass in the string and delimiter and return the elements processed out of the string.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

ScottPletcher SSC Guru Points: 101095 More actions · Answer 2

SELECT
    string_to_parse,
    CASE WHEN first_dash = 0 THEN ''
         WHEN first_dash = last_dash THEN SUBSTRING(string_to_parse, first_dash + 1, 100)
         ELSE SUBSTRING(string_to_parse, first_dash + 1, last_dash - first_dash - 1) END AS middle_string

FROM ( VALUES('2C00P02-CRIW-93201'),('ABC-7087987y83-l1'),
    (''), ('BADDATA'), ('BADDATA-CRIW-93201-OOPSTHREEDASHES'),
    ('BADDATA-CRIW:ONLY_ONE_DASH?!') ) AS sample_data(string_to_parse)
CROSS APPLY (
    /* move lookup calcs out of the main SELECT to keep the main code "cleaner" */
    SELECT CHARINDEX('-', string_to_parse) AS first_dash,
        LEN(string_to_parse) - CHARINDEX('-', REVERSE(string_to_parse)) + 1 AS last_dash
) AS ca1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

sullyman SSC Enthusiast Points: 115 More actions · Answer 3

Thanks so much guys

I think the Select MyValue = parsename(replace('2C00P02-CRIW-93201', '-', '.'), 2) will work

Some values in the table are like 123 bgbb bfbfb (without no dashes etc.) so when the above query is run it returns null which is ok. How do i omit null results from the above query

sullyman SSC Enthusiast Points: 115 More actions · Answer 4

i can achieve omitting nulls within query with the following but is there a way to omit nulls within the parsename statement itself

SELECT        PARSENAME(REPLACE(LINE, '-', '.'), 2) AS Test
FROM            dbo.[TestTable]
WHERE        (PARSENAME(REPLACE(LINE, '-', '.'), 2) IS NOT NULL)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

Do you want to just exclude the rows that do not have a '-' in them? Or do you want to return something different - or some other value?

Just to make sure you understand...PARSENAME will only work if you have no more than 4 elements in the string. If some rows can have 3 and some have 4 and others only have 2 and yet others have 5 then PARSENAME will not work.

PARSENAME does appear to be simpler to implement and use but it is much more restrictive in how it can be used. I would recommend reviewing the other solution I provided (and Scott's solution) - either can be converted into an inline-table valued function that returns all elements from your string where the first element would be the full string if no dashes were found.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

sullyman SSC Enthusiast Points: 115 More actions · Answer 6

I think excluding the rows that do not have a '-' in them would work

Perhaps i can build an IIf statement around the Parsename function ?

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

There are several ways to approach this...you can filter any rows that do not contain a dash, but if you have rows with additional dashes then you get incorrect results. You can filter those out using something like this:

Declare @testTable Table (MyData varchar(50));
 Insert Into @testTable (MyData)
 Values ('2C00P02-CRIW-93201')
      , ('2A11V01-CRIW1-93202')
      , ('2C00P02CRIW93203')
      , ('2C00P02 CRIW 93204')
      , ('2C00P02-CRIW-93205-4DASH');

 Select *
      , Test = parsename(replace(tt.MyData, '-', '.'), 2)
   From @testTable tt
  Where len(tt.MyData) - len(replace(tt.MyData, '-', '')) = 2

If you are sure there can only ever be 2 dashes if they exist:

Declare @testTable Table (MyData varchar(50));
 Insert Into @testTable (MyData)
 Values ('2C00P02-CRIW-93201')
      , ('2A11V01-CRIW1-93202')
      , ('2C00P02CRIW93203')
      , ('2C00P02 CRIW 93204');

 Select *
      , Test = parsename(replace(tt.MyData, '-', '.'), 2)
   From @testTable tt
  Where tt.MyDate Like '%-%';

Or - you can move the parsing into a cross apply and check the results:

 Select *
   From @testTable              tt
  Cross Apply (Values (parsename(replace(tt.MyData, '-', '.'), 2))) As p(MyData)
  Where p.MyData Is Not Null;

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs