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
-
1can you show us what have you tried ?Squirrel– Squirrel2019-06-11 10:30:02 +00:00Commented Jun 11, 2019 at 10:30
-
2What are the roles for the split? what version of SQL Server are you using? Please post proper sample data (as DDL+DML) and formatted text for desired results instead of images. Also, it would help to see what you've already tried so far.Zohar Peled– Zohar Peled2019-06-11 10:30:14 +00:00Commented Jun 11, 2019 at 10:30
Add a comment
|
4 Answers
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
1 Comment
Thom A
Adding some explanation to your code only answer will greatly help the OP understand what your answer does here.
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.
Comments
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 |
+----------------+-----------+--------+
Comments
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
