Split value of a column? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Split value of a column?

Author

Topic

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2012-07-01 : 08:56:46

Hello,

does a function exists to search a string and split values of a column?

Example:

4 days

I'd like to solit beetween the number and 'days' so that I can use the number for some statements and 'days' for some statements.

Kind Regdards,

Lara

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-01 : 10:28:59

If you google for string splitter, you will find a lot of resources. Most of them use something like a while loop, so the performance is likely to be not great if you have a large number of rows.

One that I use most often and does perform well is Jeff Moden's function here: http://www.sqlservercentral.com/articles/Tally+Table/72993/ Copy the code from Fig. 21 and use it. You will need a login to SQL Server Central, but it is free and very useful if you regularly work with SQL.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-01 : 11:29:32

question is whether string format is always consistent in your case so that you can apply a unified rule

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2012-07-09 : 15:22:09

Hi,

I've got the table tree with the column "description". The content is a string with the following pattern:

<content Field 1> <content Field 2> <content Field 3> <content Field 4>

The result I want is a a shown table with

Field1.....Field2.....Field3.....Field4

content......content...content....content

I could slit it with every ">", but can I delete the brackets (<>) in the result. Are such thing possible with sql?

Kind regards,

Robert

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-09 : 15:47:32

Can you post a sample of your actual data? The reason I am asking is because if it is in a valid XML format, then it is a little easier - you can use XQuery functions to parse it.

If it is not valid XML, you can still do it (in most cases, assuming the data has consistent and predictable patterns) using a splitter and using REPLACE commands.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 16:12:40

quote:
Originally posted by musclebreast

Hi,

I've got the table tree with the column "description". The content is a string with the following pattern:

<content Field 1> <content Field 2> <content Field 3> <content Field 4>

The result I want is a a shown table with

Field1.....Field2.....Field3.....Field4

content......content...content....content

I could slit it with every ">", but can I delete the brackets (<>) in the result. Are such thing possible with sql?

Kind regards,

Robert

you can. use replace() function for that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2012-07-09 : 16:30:48

Hi,

Thanks for your answer....Sorry that I need to ask again, because I don't get it. That is the function

replace(field1,'>','')

What should I replace? Maybe I described it wrong but the following content:

<content Field 1> <content Field 2> <content Field 3> <content Field 4>

is in one column.

Regards,

Lara

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 17:34:17

only you split it up. add replace(splitcolumn ,'>','') to get rid of > characters

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2012-07-09 : 18:11:48

hi,

ok..to get rid of the ">" the replace function is useful. One more question. It seems sql server doesn't have a split function...I find some on the internet, but is there not one? the Thing is I use the sql in another software and it allows only select statements...

kind regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 21:27:41

nope there's nt one. you've to write udf like below.

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2012-07-11 : 12:20:51

Thanks,

normally I work with oracle and there I can save the funtions in the database...

in sql server i load the function at fist and then i can use it...how long will sql server store the function..ist it only for a limited time?

Kind regards,

Lara

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-11 : 12:40:45

nope.once its created on db it will be present until you drop it explicitly

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2012-07-11 : 14:23:41

Thats great...to conlude..I just need to run the create function and then it's saved...Where can I get a list of all saved functions or where are they saved?

Cheers..

Lara

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-11 : 14:27:02

quote:
Originally posted by musclebreast

Thats great...to conlude..I just need to run the create function and then it's saved...Where can I get a list of all saved functions or where are they saved?

Cheers..

Lara

Under your DB name you will find a folder called User Functions or something similar...that's where to look for it.

--------------------------
Get rich or die trying
--------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-11 : 15:54:17

quote:
Originally posted by musclebreast

Thats great...to conlude..I just need to run the create function and then it's saved...Where can I get a list of all saved functions or where are they saved?

Cheers..

Lara

yep...and to call it use synatx like

SELECT * FROM dbo.Yourfunction(param1,param2,...) if its table valued
ie if it returns table
and

SELECT dbo.Yourfunction(param1,param2,..)

if its scalar valued (ie returns single value)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources