Please Help me - 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)

Please Help me

Author

Topic

sudha12345
Starting Member

47 Posts

Posted - 2009-04-24 : 02:55:12

I has data like 1101:i1, 11052:i2, 110586:i3,110:i4 as a string. i want the output individually like i1 i2 i3 i4 . how can we do it in SQL SERVER

Sudhakar

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-24 : 03:07:43

SELECT PARSENAME(REPLACE(Col1, ':', '.'), 1)
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-04-24 : 03:07:48

select substring('1101:i1',charindex(':','1101:i1')+1,len('1101:i1'))

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

sudha12345
Starting Member

47 Posts

Posted - 2009-04-24 : 03:17:19

quote:
Originally posted by Peso

SELECT PARSENAME(REPLACE(Col1, ':', '.'), 1)
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

Hi it is fine. for example the data is like 1101:1,1102:2

the data is coming like 1101,1,1102,2
but i dont want 1101,1102 do bw displayed
Please help me

Sudhakar

Purvi
Starting Member

3 Posts

Posted - 2009-04-24 : 03:19:50

i think substring('1101:i1',charindex(':','1101:i1')+1,LEN('1101:i1')) should work, it isn't working?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-24 : 03:20:33

SELECT PARSENAME(REPLACE(Col1, ':', '.'), 2)
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

sudha12345
Starting Member

47 Posts

Posted - 2009-04-24 : 03:21:19

quote:
Originally posted by senthil_nagore

select substring('1101:i1',charindex(':','1101:i1')+1,len('1101:i1'))

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

hi this is fine. But we cant hard code the columns like 1101:i1.
why because we are not sure how the data will come. can you give me reply wih out hard coding the fields like 1101:i1

Sudhakar

sudha12345
Starting Member

47 Posts

Posted - 2009-04-24 : 03:33:33

quote:
Originally posted by Peso

SELECT PARSENAME(REPLACE(Col1, ':', '.'), 2)
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

hi, his is not working

i am giving you data
'1101:1,1102:2,1103:3,1104:4' this is a text

and i want 1,2,3,4 as output

can you help me

Sudhakar

sudha12345
Starting Member

47 Posts

Posted - 2009-04-24 : 03:39:18

quote:
Originally posted by Purvi

i think substring('1101:i1',charindex(':','1101:i1')+1,LEN('1101:i1')) should work, it isn't working?

it is working.

my question is if the data is like
'1101:i1,1102:i2,1103:i3'
then how we will write the above to get it as
i1,i2,i3

Sudhakar

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-04-24 : 03:41:46

Hey Stuff the column name insted of the string...

EG:

select substring(col1,charindex(':',col1)+1,LEN(col1))

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-04-24 : 03:42:26

i think substring('1101:i1',charindex(':','1101:i1')+1,LEN('1101:i1')) should work, it isn't working?

Whats ur doubt reg this?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

sudha12345
Starting Member

47 Posts

Posted - 2009-04-24 : 03:47:36

quote:
Originally posted by senthil_nagore

Hey Stuff the column name insted of the string...

EG:

select substring(col1,charindex(':',col1)+1,LEN(col1))

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

but in the column only the data is like that 1101:i1,1102:i2

can it works if the data in the column is like above

Sudhakar

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-04-24 : 03:50:11

Ya it will work! Try it...

If u have problem show ur table with some sample data!!!!!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-04-24 : 04:39:16

try this also,

select right(ltrim(rtrim(ur_column)),len(ltrim(rtrim(ur_column)))-charindex(':',ur_column)) from ur_table

tanx....

matty
Posting Yak Master

161 Posts

Posted - 2009-04-24 : 05:24:05

First you should split the string.
Refer http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

And then use a split function in the below query

SELECT PARSENAME(REPLACE(data, ':', '.'), 1)
FROM TableName
CROSS APPLY yourFunctionName(',',ColumnName)

quote:
Originally posted by sudha12345

quote:
Originally posted by Peso

SELECT PARSENAME(REPLACE(Col1, ':', '.'), 2)
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

hi, his is not working

i am giving you data
'1101:1,1102:2,1103:3,1104:4' this is a text

and i want 1,2,3,4 as output

can you help me

Sudhakar

sudha12345
Starting Member

47 Posts

Posted - 2009-04-24 : 05:42:02

quote:
Originally posted by matty

First you should split the string.
Refer http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

And then use a split function in the below query

SELECT PARSENAME(REPLACE(data, ':', '.'), 1)
FROM TableName
CROSS APPLY yourFunctionName(',',ColumnName)

quote:
Originally posted by sudha12345

quote:
Originally posted by Peso

SELECT PARSENAME(REPLACE(Col1, ':', '.'), 2)
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

hi, his is not working

i am giving you data
'1101:1,1102:2,1103:3,1104:4' this is a text

and i want 1,2,3,4 as output

can you help me

Sudhakar

hi matty,

if i has the data like '1101:1,1102:2,1103:3,1104:4' text then can we
split the data like 1101,1,1102,2,1103,3,1104,4 and then insert the
data 1101,1102,1103,1104 into one column and 1,2,3,4 into another column please help

Sudhakar

matty
Posting Yak Master

161 Posts

Posted - 2009-04-24 : 06:08:02

Did you check the link i provided? You will see lot of 'string split function'.
And then use

SELECT PARSENAME(REPLACE(data, ':', '.'), 1) AS col1, PARSENAME(REPLACE(data, ':', '.'), 2) AS col2
FROM TableName
CROSS APPLY yourFunctionName(',',ColumnName)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources