Select all but..... - 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)

Select all but.....

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-12-19 : 12:16:41

I have a field thats varchar field, that stores a number with a - @ the end of the number ex:

123456-

I want to select just the numbers and not the -, it that possible, the leghth of the number will vary

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 12:21:35

SELECT LEFT(Col1, CHARINDEX('-', Col1) - 1)
FROM Table1

E 12°55'05.25"
N 56°04'39.16"

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-12-19 : 12:31:24

Appreciate the quick response, getting the following:

Invalid length parameter passed to the SUBSTRING function

after running

select top 10 LEFT(netcash, CHARINDEX('-',netcash) -1) from transactions

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-19 : 12:31:44

LEFT(Col1, LEN(Col1) - 1)

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-12-19 : 12:35:02

THANKS, THAT WORKED GREAT. HAPPY HOLIDAYS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 12:36:57

Then all records DO NOT HAVE a trailing hyphen.
Are you kidding us? Since this is the case, you should have told us.

SELECT	LEFT(Col1, LastPos)
FROM	(
		SELECT	CASE CHARINDEX('-', Col1)
				WHEN 0 THEN LEN(Col1)
				ELSE CHARINDEX('-', Col1) - 1
			END AS LastPos,
			Col1
		FROM	Table1
	) AS d

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 12:37:39

quote:
Originally posted by duhaas

THANKS, THAT WORKED GREAT. HAPPY HOLIDAYS

Not for the records that do NOT have a trailing hyphen.
Then you just truncated the column.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 12:40:59

Here is the proof!

-- Prepare sample data
DECLARE	@Sample TABLE (Data VARCHAR(20))

INSERT	@Sample
SELECT	'123456-' UNION ALL
SELECT	'abcdefghijklmnop-' UNION ALL
SELECT	'7890' UNION ALL
SELECT	'zyx'

-- Show the expected output
SELECT	Data AS OriginalValue,
	LEFT(Data, LastPos) AS Peso,
	LEFT(Data, LEN(Data) - 1) AS Visakh16
FROM	(
		SELECT	CASE CHARINDEX('-', Data)
				WHEN 0 THEN LEN(Data)
				ELSE CHARINDEX('-', Data) - 1
			END AS LastPos,
			Data
		FROM	@Sample
	) AS d

E 12°55'05.25"
N 56°04'39.16"

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-12-19 : 12:43:41

I see your point, using

select top 10 LEFT(netcash, len(netcash) -1) from transactions

removes the last number of strings that dont have a - @ the end. Spoke too soon before. example would be 85928.70 goes to 85928.7

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 12:45:55

See my proof posted 12/19/2007 : 12:40:59

E 12°55'05.25"
N 56°04'39.16"

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-12-19 : 12:47:30

Understood, I apologize for not being perfectly clear with my question, this solved my problem:

SELECT	LEFT(netcash, LastPos)
FROM	(
		SELECT	CASE CHARINDEX('-', netcash)
				WHEN 0 THEN LEN(netcash)
				ELSE CHARINDEX('-', netcash) - 1
			END AS LastPos,
			netcash
		FROM	transactions
	) AS d

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 12:50:05

Great!
Good luck and have a nice holiday.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 12:51:00

Another twist is that if ALL records are "numeric", ie can be treated as numeric, do this

SELECT ABS(NetCash)
FROM Transactions

E 12°55'05.25"
N 56°04'39.16"

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-12-19 : 14:23:53

What would your advice be if I wanted to update all the columns in the table so that the - sign showed up @ the front instead of the end??? So instead of 123456-, it would be -123456.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 15:15:00

UPDATE Table1
SET Col1 = '-' + LEFT(Col1, LEN(Col1) - 1)
WHERE Col1 LIKE '%-'

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-20 : 02:28:44

or

UPDATE Table1
SET Col1 = Replace(Col1,'-','')*-1
WHERE Col1 LIKE '%-'

Madhivanan

Failing to plan is Planning to fail

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-12-20 : 03:10:32

Another way,

Select Case When Substring(Data,Charindex('-',Data),len(Data))='-' Then
Substring(Data,Charindex('-',0),Len(Data)) Else Data End From @sample

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-20 : 03:22:55

quote:
Originally posted by ayamas

Another way,

Select Case When Substring(Data,Charindex('-',Data),len(Data))='-' Then
Substring(Data,Charindex('-',0),Len(Data)) Else Data End From @sample

There can be N number of ways in this case

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources