Split Function

[WelshyWizard]

Hi all,

I am looking to split a value held in a SQL Server 2000 table, and the split results need to be returned into 2 new fields on the same row.

For example:

Field 1 = 900X10FLAT

Field 2 = 900

Field 3 = 10

The value in field 1 should be split into the other 2 fields. These values will always be numerical but the end characters may be different everytime. There will always be the character 'X' between the numbers.

However, the value will not always need to be split. Whether the value is split or not will be dependant upon certain values held in field 4. For example. If field 4 = TEST then split the values, else don't split them.

Ideally I need to create this in a stored procedure that I'll call from a .NET application.

Cheers

Today is the tomorrow you worried about yesterday - and all is well.....

 

[ca8msm]

You can use the SUBSTRING function to get the specific characters. You then just need to append a WHERE clause to only get the records that you need.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[ptheriault]

Is the value after the X always two digits? If so this is what Mark had in mind.

DECLARE @table table (col1 varchar(20))

INSERT INTO @table VALUES( '900X10FLAT')
INSERT INTO @table VALUES ('TEST')



SELECT  Col1 as Field1
       ,SUBSTRING(col1, 1,CHARINDEX('X', COL1)-1) as Field2
       ,SUBSTRING(col1,CHARINDEX('X', COL1)+1,2) as Field3
FROM @table
WHERE CHARINDEX('X', col1) > 0

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[WelshyWizard]

Unfortunately the value after the X could be 2 digits, 3 digits or even a decimal value! (i.e. 25.5)

Any ideas how I'd do this?

Thanks for the help so far.

Today is the tomorrow you worried about yesterday - and all is well.....

 

[ca8msm]

You could use the REPLACE function to remove the FLAT part of the string and then you can work out how long the last number is by using the LEN function.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[WelshyWizard]

Sorry, I should have added that ther end will not always be FLAT... It could be any number of things!

Today is the tomorrow you worried about yesterday - and all is well.....

 

[WelshyWizard]

Thanks for your time on this guys... I created my solution as follows:

UPDATE IMPORT
SET field2 = SUBSTRING(field1, 1, CHARINDEX('X',field1)-1)
, field3 = substring(substring(field1,charindex('X',field1)+1,len(feild1)),1,patindex('%[A-Z]%',substring(field1,charindex('X',field1)+1,len(field1)))-1)

Please feel free to comment upon this if you feel that there are any improvements or additions that can be made.

Today is the tomorrow you worried about yesterday - and all is well.....