String data - 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

SQL Server 2005 Forums

Transact-SQL (2005)

String data

Author

Topic

KrishnaDubey
Starting Member

19 Posts

Posted - 2011-08-24 : 11:47:47

I want to split some string. Like :
sddsd sdsd,asdasd dsad

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 11:59:29

is it split up based on space character?

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-26 : 19:20:12

use fnParseString from
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-27 : 04:58:29

use PARSENAME if it has only four parts maximum

http://msdn.microsoft.com/en-us/library/ms188006.aspx

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 10:28:16

try

select * from dbo.ParseValues('sdfd adsads 21fdd',' ')f

ParseValues can be found in below link

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

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 10:58:16

thats ok. you can use any delimiters like space, | etc just make sure you pass required delimiter to function(see my ex i've used space above).

for getting it to columns use like


UPDATE p
SET Column1=Col1,
Column2 = Col2,
Column3 =Col3,
Column4 = Col4
FROM 
(
SELECT t.Column1,t.Column2,t.Column3,t.Column4,
MAX(CASE WHEN ID=1 THEN Val ELSE NULL END) AS Col1,
MAX(CASE WHEN ID=2 THEN Val ELSE NULL END) AS Col2,
MAX(CASE WHEN ID=3 THEN Val ELSE NULL END) AS Col3,
MAX(CASE WHEN ID=4 THEN Val ELSE NULL END) AS Col4
Table t
CROSS APPLY dbo.ParseValues (t.AddressCol,' ')f
GROUP BY t.AddressCol,t.Column1,t.Column2,t.Column3,t.Column4
)p

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-29 : 13:13:01

see:-


CREATE TABLE t1
(
AddCol varchar(1000),
Column1 varchar(100),
Column2 varchar(100),
Column3 varchar(100),
Column4 varchar(100)
)


INSERT t1(AddCol)
SELECT '14 LITTLETON DR' UNION ALL
SELECT '786 COUNTY Apt12 RD' UNION ALL
SELECT '45 LONGBOAT DR' UNION ALL
SELECT '65 PAPERMILL RD' UNION ALL
SELECT '24 HELEN ST' UNION ALL
SELECT '79 WINDSHORE DR'

select * from t1

UPDATE q
SET Column1=Col1,
Column2 = Col2,
Column3 =Col3,
Column4 = Col4
FROM t1 q
JOIN(
SELECT t.AddCol,t.Column1,t.Column2,t.Column3,t.Column4,
MAX(CASE WHEN ID=1 THEN Val ELSE NULL END) AS Col1,
MAX(CASE WHEN ID=2 THEN Val ELSE NULL END) AS Col2,
MAX(CASE WHEN ID=3 THEN Val ELSE NULL END) AS Col3,
MAX(CASE WHEN ID=4 THEN Val ELSE NULL END) AS Col4
FROM t1 t
CROSS APPLY dbo.ParseValues (t.AddCol,' ')f
GROUP BY t.AddCol,t.Column1,t.Column2,t.Column3,t.Column4
)p
ON p.AddCol = q.AddCol



select * from t1

output
-----------------------------------------------

AddCol                         Column1           Column2                   Column3                      Column4
----------------------------- ----------------- -------------------------- ----------------------- --------------
14 LITTLETON DR               14                LITTLETON                  DR                      NULL
786 COUNTY Apt12 RD           786               COUNTY                     Apt12                   RD
45 LONGBOAT DR                45                LONGBOAT                   DR                      NULL
65 PAPERMILL RD               65                PAPERMILL                  RD                      NULL
24 HELEN ST                   24                HELEN                      ST                      NULL
79 WINDSHORE DR               79                WINDSHORE                  DR                      NULL

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 10:02:23

Are you using SQL Server 2005 or above? run below and post the result


SELECT @@VERSION
EXEC sp_dbcmptlevel 'your db name'

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 10:22:02

please post the result of suggested query.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 10:31:58

exactly what i thought!! Now I hope you understood that you're not on sql 2005
so you're on sql 2000 and hence APPLY cant be used.

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-30 : 10:38:46

quote:
Originally posted by KrishnaDubey

Thanks you, but I am getting error at the runtime.

I want to run below line :

Select * from dbo.fnParseList (' ','sdfd adsads 21fdd')

Error was:
Invalid length parameter passed to the substring function.

quote:
Originally posted by khtan

use fnParseString from
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

Regards,
Krishna Ddubey

You used the wrong function. Anyway for fnParseString(),
Change the @Text data type to varchar(8000)


CREATE FUNCTION dbo.fnParseString
(
	@Section SMALLINT,
	@Delimiter CHAR,
	@Text TEXT varchar(8000)
)

Example usage


select	dbo.fnParseString(-1, ' ', '786 COUNTY Apt12 RD'),
	dbo.fnParseString(-2, ' ', '786 COUNTY Apt12 RD'),
	dbo.fnParseString(-3, ' ', '786 COUNTY Apt12 RD'),
	dbo.fnParseString(-4, ' ', '786 COUNTY Apt12 RD')

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 13:14:55

you can use this as long as address has maximum of four parts


CREATE TABLE t1
(
AddCol varchar(1000),
Column1 varchar(100),
Column2 varchar(100),
Column3 varchar(100),
Column4 varchar(100)
)


INSERT t1(AddCol)
SELECT '14 LITTLETON DR' UNION ALL
SELECT '786 COUNTY Apt12 RD' UNION ALL
SELECT '45 LONGBOAT DR' UNION ALL
SELECT '65 PAPERMILL RD' UNION ALL
SELECT '24 HELEN ST' UNION ALL
SELECT '79 WINDSHORE DR'

select * from t1

UPDATE q
SET Column1=COALESCE(PARSENAME(REPLACE(AddCol,' ','.'),4),PARSENAME(REPLACE(AddCol,' ','.'),3),PARSENAME(REPLACE(AddCol,' ','.'),2),PARSENAME(REPLACE(AddCol,' ','.'),1)),
Column2 =CASE WHEN PARSENAME(REPLACE(AddCol,' ','.'),4) IS NOT NULL THEN PARSENAME(REPLACE(AddCol,' ','.'),3)
              WHEN PARSENAME(REPLACE(AddCol,' ','.'),3) IS NOT NULL THEN PARSENAME(REPLACE(AddCol,' ','.'),2)
              WHEN PARSENAME(REPLACE(AddCol,' ','.'),2) IS NOT NULL THEN PARSENAME(REPLACE(AddCol,' ','.'),1)
              ELSE NULL
         END ,
Column3 =CASE WHEN PARSENAME(REPLACE(AddCol,' ','.'),4) IS NOT NULL THEN PARSENAME(REPLACE(AddCol,' ','.'),2)
              WHEN PARSENAME(REPLACE(AddCol,' ','.'),3) IS NOT NULL THEN PARSENAME(REPLACE(AddCol,' ','.'),1)
              ELSE NULL
         END ,
Column4 =CASE WHEN PARSENAME(REPLACE(AddCol,' ','.'),4) IS NOT NULL THEN PARSENAME(REPLACE(AddCol,' ','.'),1)
              ELSE NULL
         END
FROM t1 q




select * from t1

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-30 : 21:08:31

quote:
Originally posted by KrishnaDubey

Great, But it works for a single value in a time. I have a table having 1MM records(It can be more than 1MM) and has address column. I have to split all those values. In this case what i can do?

quote:
Originally posted by khtan

quote:
Originally posted by KrishnaDubey

Thanks you, but I am getting error at the runtime.

I want to run below line :

Select * from dbo.fnParseList (' ','sdfd adsads 21fdd')

Error was:
Invalid length parameter passed to the substring function.

quote:
Originally posted by khtan

use fnParseString from
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

Regards,
Krishna Ddubey

You used the wrong function. Anyway for fnParseString(),
Change the @Text data type to varchar(8000)

CREATE FUNCTION dbo.fnParseString
(
@Section SMALLINT,
@Delimiter CHAR,
@Text ~~TEXT~~ varchar(8000)
)

Example usage

select dbo.fnParseString(-1, ' ', ~~'786 COUNTY Apt12 RD'~~yourcolumn),
dbo.fnParseString(-2, ' ', ~~'786 COUNTY Apt12 RD'~~yourcolumn),
dbo.fnParseString(-3, ' ', ~~'786 COUNTY Apt12 RD'~~yourcolumn),
dbo.fnParseString(-4, ' ', ~~'786 COUNTY Apt12 RD'~~yourcolumn)
from yourtable

KH
[spoiler]Time is always against us[/spoiler]

Regards,
Krishna Ddubey

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-31 : 02:21:31

[code]
select MAX(cnt)
FROM
(SELECT LEN(AddCol)-LEN(REPLACE(AddCol,' ','')) AS cnt
FROM table )t
[/code]

will give you maximum part upto which address can go

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-31 : 02:41:05

quote:
Originally posted by KrishnaDubey

but next time it can be increased because it's depend on the client data

Regards,
Krishna Ddubey

So how is your front end application going to handle this ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-31 : 03:01:49

you will need to use Dynamic SQL to write your query. Take a look at http://www.sommarskog.se/dynamic_sql.html

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-31 : 04:08:40

quote:
Originally posted by KrishnaDubey

I want to lock this topic now.

quote:
Originally posted by KrishnaDubey

Thanks, Can you tell me how I can locked tis topic.
quote:
Originally posted by khtan

you will need to use Dynamic SQL to write your query. Take a look at http://www.sommarskog.se/dynamic_sql.html

KH
[spoiler]Time is always against us[/spoiler]

Regards,
Krishna Ddubey

Regards,
Krishna Ddubey

just modify header to include resolved

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources