Unstring text field?

Question

Post reply

Unstring text field?

Bob Bridges-173967

SSCrazy

Points: 2094
More actions
October 12, 2007 at 8:13 pm

#62173

Greetings to the group... I'm trying something but can't find any references on it on the web... maybe someone here can help me.
I have a text field with a value of XXXXX.XXXXXXX
Is it possible to unstring this field to select only the data after the period or only the data before the period?
Thanks in advance.
Bob

Viewing 15 posts - 1 through 15 (of 19 total)

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

What datatype is the column, really? Is it TEXT, VARCHAR, NVARCHAR, or ???

--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)

Bob Bridges-173967 SSCrazy Points: 2094 More actions · Answer 2

Bob Bridges-173967

SSCrazy

Points: 2094

October 13, 2007 at 4:06 am

#741781

It is a char(35) fieldtype

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

The easiest way to split limited (up to 4 parts) period-delimited text of this nature is to use PARSENAME. Look it up in Books Online for a full explanation.

Here's a short tutorial snippet for PARSENAME...

[font="Courier New"]DECLARE&nbsp@col&nbspVARCHAR(35)

&nbsp&nbsp&nbsp&nbspSET&nbsp@Col&nbsp=&nbsp'Part4.Part3.Part2.Part1'

&nbspSELECT&nbspPARSENAME(@Col,1)&nbspAS&nbspPart1,

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspPARSENAME(@Col,2)&nbspAS&nbspPart2,

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspPARSENAME(@Col,3)&nbspAS&nbspPart3,

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspPARSENAME(@Col,4)&nbspAS&nbspPart4[/font]

Here's how to solve your immediate problem using PARSENAME...

[font="Courier New"]&nbspSELECT&nbspPARSENAME(somecolname,1)&nbspAS&nbspPart1,

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspPARSENAME(somecolname,2)&nbspAS&nbspPart2

&nbsp&nbsp&nbspFROM&nbspsometable[/font]

--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)

Bob Bridges-173967 SSCrazy Points: 2094 More actions · Answer 4

Jeff,

This is exactly what I needed. Thanks very much!

Bob

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Great! Thanks for the feedback, Bob!

--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)

Phil Factor SSC-Insane Points: 20244 More actions · Answer 6

Jeff,

This is really ingenious! It would never have occurred to me. However, I hope that nobody gets too enthusiastic about using it as a general way of parsing data.

[font="Courier New"]

DECLARE @col CHAR(35)

--the first parameter is supposed to be an sysname representing an 'object name'

-- and there is some testing that takes place. Just to illustrate...

--little change

SET @Col = 'Part4.Part[3].Part2.Part1'

SELECT PARSENAME(@Col,1) AS Part1,

PARSENAME(@Col,2) AS Part2,

PARSENAME(@Col,3) AS Part3,

PARSENAME(@Col,4) AS Part4

--fistfull of nulls

SET @Col = 'Part4.Part3.Part2.Part1.'

SELECT PARSENAME(@Col,1) AS Part1,

PARSENAME(@Col,2) AS Part2,

PARSENAME(@Col,3) AS Part3,

PARSENAME(@Col,4) AS Part4

--another fistfull of nulls

SET

@Col = 'Part4.Part[3].Part2.Part1'

--safer way of getting everything before the delimiter

SELECT SUBSTRING(@col,1,CHARINDEX('.', @Col+'.')-1)

--safer way of getting everything after the delimiter

SELECT STUFF ( @col, 1, CHARINDEX('.', @Col+'.'), '' )

[/font]

Best wishes,
Phil Factor

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Agreed... 🙂

--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)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

The other way to do that is to use regular expressions, especially when you get into really ugly string matching/parsing. Setting up the regex methods are damn fast too if you're willing to use CLR (same perf or better than the string functions required in T-SQL).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Heh... too bad I don't have SQL Server 2k5, yet... would be an interesting test 'cause, you're right, Regex is very fast.

The fastest option, though, would be to have correctly split data to begin with 😉

--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)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

Jeff Moden (10/15/2007)
Heh... too bad I don't have SQL Server 2k5, yet... would be an interesting test 'cause, you're right, Regex is very fast.
The fastest option, though, would be to have correctly split data to begin with 😉

In the fairy tale land of perfectly formatted data, and foreign data that matches your specs exactly? :w00t: That would be a nice place to be.

Most days, though - I find myself myself humming the oompah lumpah song while I roll the blueberry girl down to the juicers (extract the foregin data out of fields the users have hijacked for purposes other than they were intended for).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Lowell SSC Guru Points: 323504 More actions · Answer 11

Jeff Moden (10/15/2007)
Heh... too bad I don't have SQL Server 2k5, yet... would be an interesting test 'cause, you're right, Regex is very fast.
The fastest option, though, would be to have correctly split data to begin with 😉

the DBA toolkit here on SSC has regular expressions as extended stored procedures for SQL2K; i use it all the time.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Adam Haines SSC-Insane Points: 23217 More actions · Answer 12

If the text only has one period you could use the following:

DECLARE @Col VARCHAR(35)

SET @Col = 'ABCDEFGHIJKLMNOPQRST.0987654321'

SELECT left(@Col,charindex('.',@Col)-1),

right(@Col,len(@Col) - charindex('.',@Col))

My blog: http://jahaines.blogspot.com

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 13

Lowell (10/15/2007)
the DBA toolkit here on SSC has regular expressions as extended stored procedures for SQL2K; i use it all the time.

Thanks for the tip, Lowell...

--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)

Bob Bridges-173967 SSCrazy Points: 2094 More actions · Answer 14

I've got a problem that I can't figure out... I'm using the following as suggested to get the data to the right of the delimiter...

(SELECT STUFF(GL_SWT,1,CHARINDEX('.', @col+'.'),'')

It works fine when there are 5 characters to the left of the delimiter but when I have 6 characters to the left of the delimiter, the result set includes the delimiter.

Any ideas?

Thanks,

Bob