Split fields sql 2017

Question

Split fields sql 2017

tylerschuler75

SSC Enthusiast

Points: 113
More actions
July 3, 2024 at 4:09 pm

#4433225

I am trying to split fields in sql 2017. The column holds the following. I am pulling the first 50 characters and now I need to split those into 2 fields.
This is a test name1 3006 31
This is a test nameofsecondentry wls 37
Here is my Query to pull the data.
select matterinfosortname, SUBSTRING(matterinfosortname, 1, 50) from mattinf
where 1=1
and matterinfomatternum NOT LIKE '%.%'
and matterinfostatus = 0
This gives me the following:
This is a test name1 3006
This is a test nameofsecondentry wls
I need to split that data into 2 columns.
Any help would be greatly appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

Assuming the parts to be split are: 1) the sentence on the left, and 2) the single string on the right. It appears after the initial 50 character split of the original string there are extra space characters on the end of the single string on the righthand side. To get rid of those spaces this query uses RTRIM. Then it uses REVERSE on the trimmed string and CHARINDEX to find the first (reversed) space

with data_cte as (
    select 'This is a test name1                          3006                  31' matterinfosortname
    union all
    select 'This is a test nameofsecondentry      wls                  37')
select *,
       len(substr.string) len_string,
       left(substr.string, len(substr.string)-space_char.ndx+1) first_string_val,
       right(substr.string, space_char.ndx-1) scond_string_val
from data_cte
     cross apply (values (rtrim(left(matterinfosortname, 50)))) substr(string)
     cross apply (values (charindex(' ', reverse(substr.string)))) space_char(ndx);

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

ScottPletcher SSC Guru Points: 101095 More actions · Answer 2

The data seemed inconsistent / "impossible" to me. How can the left-most 50 bytes be '3006' when that doesn't appear in the original value at all??

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".

tylerschuler75 SSC Enthusiast Points: 113 More actions · Answer 3

tylerschuler75

SSC Enthusiast

Points: 113

July 5, 2024 at 4:08 pm

#4433631

Thank you Steve. This works perfectly.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

Pretty cool, @Steve.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)