SPLIT(value,char,0)

[qwer]

hello

if a column value = name (anything)
i want to get name

how can do it ?

SELECT SPLIT( name, '(' , 0) FROM tableNames

i want to get all what is before the first bracket (
but sometimes there is no brackets then i want to get all


thank you

[rmiao]

Which rdbms do you use?

[MAK]

If it is sql server

declare @a varchar(100)
set @a='name (anything)'
select left(@a,charindex('(',@a,0)-1)

Most of the RDBMS/application languages has inbuilt functions like LEFT,Substring, mid, instr.....

[skhanal]

Extending Mak's solution for the case where there is no (

declare @a varchar(100)
set @a='name (anything)'

select case
when charindex('(',@a,0) > 0 then left(@a,charindex('(',@a,0)-1)
else @a
end

[qwer]

but how do you use it ?


SELECT users.name, users.alias FROM users


if i want to apply the filter on users.name ?

[MAK]

select case
when charindex('(',name,0) > 0 then left(name,charindex('(',name,0)-1)
else name end from users

[qwer]

SELECT TOP 100 PERCENT CASE WHEN charindex('(', name, 0) > 0 THEN LEFT(name, charindex('(', name, 0) - 1) ELSE name END, alias
FROM users


doesn't work

enterprise manager doesn't accept the syntax

[skhanal]

Why do you need TOP 100 Percent?.

SELECT "Name"= CASE WHEN charindex('(', name, 0) > 0 THEN LEFT(name, charindex('(', name, 0) - 1) ELSE name END, alias
FROM users

[MAK]

SELECT TOP 10 PERCENT [Name] = CASE WHEN charindex('(', name, 0) > 0 THEN LEFT(name, charindex('(', name, 0) - 1) ELSE name END
FROM users

[qwer]

Enterprise Manager has added the PERCENT , me not , i need all the rows

the following table

CREATE TABLE [dbo].[Config] (
	[id_Config] [int] IDENTITY (1, 1) NOT NULL ,
	[nom] [varchar] (100) COLLATE French_CI_AS NULL ,
	[valeur] [varchar] (255) COLLATE French_CI_AS NULL
) ON [PRIMARY]

the column [nom] can store sometimes datas with (.....)
like >> Paul Dubois (ingenieur)

but not allways !

i must clean the [nom] and get >> Paul Dubois
and remove any space after the Dubois if any (TRIM)

SELECT nom, valeur FROM Config

I cannot find a way to get my datas with your code, AND Enterprise Manager don't even accepts the syntax if I try to create a View pasting the code

thank you

[MAK]

use query analyzer.

[qwer]

it works !!!

that stupid enterprise manager :-)


thank you

[rmiao]

Ya since em is not designed for that kind of process.

[qwer]

I am still having a problem .. i must compare the splitted value with a clean value in another table

SELECT (CASE WHEN charindex('(',Item.IValue,0) > 0 THEN left(Item.IValue,charindex('(',Item.IValue,0)-1) ELSE Item.IValue END ) AS Computer, 
Etiquettes_1.valeur AS Etiquette
FROM dbo.Etiquettes Etiquettes_1 RIGHT OUTER JOIN
dbo.Item Item ON Etiquettes_1.id_Station = Item.IValue;

but in that line : Etiquettes_1.id_Station = Item.IValue

I must compare : Etiquettes_1.id_Station = Computer the splitted value

how can I do it ?


thank you