CODEWITHSUNDEEP
sqlsql-servert-sql
my trung
my trung

Reputation: 57

How to Split String SQL from to?

how to split one value from sql?

EX: column SQL

Now, I want split to

Code:

declare @ND nvarchar(max)= N'AA (AA11) [37100]'
select substring(@ND,1,patindex('%[0-9]%',@ND)-2) as Name
      ,substring(@ND,patindex('%[0-9]%',@ND),len(@ND)) as Number

Upvotes: 1

Views: 114

Answers (4)

Ilyes
Ilyes

Reputation: 14928

You can do that by using CHARINDEX(), REPLACE(), LEN() and SUBSTRING() functions as

SELECT Str FullName,
       REPLACE(Str, SUBSTRING(Str, CHARINDEX('[', Str), CHARINDEX(']', Str)), '') Name,
       SUBSTRING(Str, CHARINDEX('[', Str) + 1, LEN(Str) - CHARINDEX('[', Str)-1) Number
FROM (VALUES('BBB(15CC)[222]'), ('AA[1111]')) T(Str);

OR

;WITH CTE AS
(
  SELECT Str FullName,
         LEFT(Str, CHARINDEX('[', Str) -1) Name
  FROM (VALUES('BBB(15CC)[222]'), ('AA[1111]')) T(Str)
)
SELECT FullName,
       Name,
       REPLACE(REPLACE(FullName, Name + '[', ''), ']', '') Number
FROM CTE;

Returns:

+----------------+-----------+--------+
|    FullName    |   Name    | Number |
+----------------+-----------+--------+
| BBB(15CC)[222] | BBB(15CC) |    222 |
| AA[1111]       | AA        |   1111 |
+----------------+-----------+--------+

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

In your sample data, the last 6 characters are always square braces with the number. If this is always the case, you can use simpler logic:

select left(fullname, len(fullname) - 7),
       left(right(fullname, 5), 4)
from @t;

Here is a db<>fiddle.

For a more general case, I would also want to handle the situation where there is no [ in the string without generating an error:

select left(fullname, charindex('[', fullname + '[') - 1),
       replace(stuff(fullname, 1, charindex('[', fullname + '['), ''), ']', '')
from @t;

Here is another db<>fiddle.

Upvotes: 1

Sreenu131
Sreenu131

Reputation: 2516

Using Xml conversion we can get the expected result

Declare @t table (fullname varchar(50))
insert into @t values ('AA [1111]')
insert into @t values ('BB(15CC) [2222]')


SELECT DISTINCT Fullname,
       Split.a.value('/S[1]','nvarchar(100)') AS Name,
       Split.a.value('/S[2]','nvarchar(100)') AS Number
FROM
(
SELECT Fullname,
        CAST('<S>'+REPLACE(REPLACE(REPLACE(Fullname,' ','</S><S>')+'</S>','[',''),']','') AS XML ) AS FullnameData
FROM @t
)AS A
CROSS APPLY FullnameData.nodes('S') AS Split(a)

Result

Fullname            Name        Number
----------------------------------------
AA [1111]           AA          1111
BB(15CC) [2222]     BB(15CC)    2222

Upvotes: 0

Red Devil
Red Devil

Reputation: 2393

Declare @t table (fullname varchar(50))

insert into @t values ('AA [1111]')
insert into @t values ('BB(15CC) [2222]')

select 
    fullname,
    substring(fullname,1,CHARINDEX('[',fullname)-1) Name,
    replace(substring(fullname,CHARINDEX('[',fullname)+1,len(fullname)),']','') as number 
from @t

Upvotes: 1

Related Questions